Macro that Uses Current Date & Time Naming Convention to Save as Filetype

[censo]

Greetings,

The following code successfully creates new workbooks

    Sheets("AE Upload").Copy
    Cells(1, 1).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Range("A1").Select

and separately

    Sheets("FFIL").Copy
    Cells(1, 1).Sort Key1:=Columns(4), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Range("A1").Select

However, I would like the macro to save the file in the following naming convention: mm.dd.yy-0000-(worksheet name).
- the "0000" is in 24-hr time format
- I have two file format requirements (for separate reports). So please include the code for .txt file format, and separately include the code for .csv file format

Example1: 07.16.21-0228-AE Upload.csv
Example2: 07.16.21-0228-FFIL.txt

Thanks.

 

[Joe4]

Here is a code snippet showing you how you can build those file names:

    Dim fname1 As String
    Dim fname2 As String
    
    fname1 = Format(Now, "mm.dd.yy-hhnn") & "-AE Upload.csv"
    fname2 = Format(Now, "mm.dd.yy-hhnn") & "-FFIL.txt"
    
    MsgBox fname1
    MsgBox fname2

 

[censo]

Thank you for your contribution. I'm not experienced enough to know what to do with the snippet (which is why I provided the existing code I have so that the solution can be integrated in with that). Nevertheless I attempted the code you provided with what made sense to me and these are the results:

  Sheets("AE Upload").Copy
    Cells(1, 1).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes

    Dim fname1 As String
    fname1 = Format(Now, "mm.dd.yy-hhnn") & "-AE Upload.csv"
    MsgBox fname1
    Range("A1").Select

1. The macro created a new workbook as expected
2. The new worksheet is correctly labeled "AE Upload"
3. A dialog box did pop-up but when I clicked on 'OK' nothing happened and the workbook name is "Book1"

I don't prefer a pop-up box, just the new workbook created in the naming convention and file format referenced above.

 

[Joe4]

Please post your ENTIRE code, so we can see how you are creating thw workbooks.

 

[censo]

Please post your ENTIRE code, so we can see how you are creating thw workbooks.

Sub AE_Upload()

ActiveSheet.AutoFilterMode = False
    'Begins Data Transfer to AE Upload tab
  
    With Worksheets("SKUG.CSV").Range("A1").CurrentRegion
        .Offset(1).Columns("G").Copy Worksheets("AE UPLOAD").Range("A2")
        .Offset(1).Columns("M").Copy Worksheets("AE UPLOAD").Range("B2")
    End With
  
    With Worksheets("AE UPLOAD").Range("B2").CurrentRegion.Columns("B")
        .Offset(1, 1).Resize(.Rows.Count - 1, 1).Value = "USD"
        End With
    
  
    Sheets("AE Upload").Copy
    Cells(1, 1).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Range("A1").Select

    Application.ScreenUpdating = True
End Sub

 

[Joe4]

OK, I don't see anything in your code that currently creates new worksheets or workbooks.

If you want to save each sheet as a CSV or Text file, you can get much of the code that you need for that by using the Macro Recorder.
Simply turn on the Macro Recorder, and go to the "AE Upload" tab and do a "SaveAs" and save as a CSV.
Then go to your other tab, and save that in your desired Text file format.
Then stop the Macro Recorder.

Then you can view the VBA code that you recorded, and replace the hard-coded file path/names with the fname1 and fname2 variables we came up with.
That should give you the dynamic code that you are looking for.

The Macro Recorder is a great tool for getting snippets of VBA code like this.

 

[censo]

OK, I don't see anything in your code that currently creates new worksheets or workbooks.

If you want to save each sheet as a CSV or Text file, you can get much of the code that you need for that by using the Macro Recorder.
Simply turn on the Macro Recorder, and go to the "AE Upload" tab and do a "SaveAs" and save as a CSV.
Then go to your other tab, and save that in your desired Text file format.
Then stop the Macro Recorder.

Then you can view the VBA code that you recorded, and replace the hard-coded file path/names with the fname1 and fname2 variables we came up with.
That should give you the dynamic code that you are looking for.

The Macro Recorder is a great tool for getting snippets of VBA code like this.

The following line of code creates the new workbook

Sheets("AE Upload").Copy

I didn't think it would either since it seems so simple but try it with any of your worksheets and you'll see.

I wholeheartedly agree with the macro recorder, that's how I've been able to piece together solutions for the complex environment I have. I didn't think to use it this time for performing the Save As function for naming the workbook and choosing the appropriate filename but I'll give it a shot.

Nevertheless, do you know why the proposed solution you contributed did not generate the intended result?

 

[Joe4]

Nevertheless, do you know why the proposed solution you contributed did not generate the intended result?

I think you may have misunderstood what I posted. That does NOT save the worksheet/workbook. All that it does is build the file name that you want, and then save it as a variable (I thought that is where you were having trouble - in dynamically creating the file name, not in the saving part). The thinking then is that you can use this variables in a "SaveAs" command to save it with the name you like.

Instead of doing it the way you initially tried, I would recommend going to that sheet and doing a "SaveAs", and save it as a CSV file.
If you use the Macro Recorder, you will have to give it some hard-coded file name when saving it.
Then afterwards, when stopping the Macro Recorder and viewing the code, you can replace the hard-coded name with the value I showed you how to calculate.

Make sense?

 

[censo]

Sorry for the delay in getting back.

The following is the code that got me exactly what I'm looking for

 Sheets("AE Upload").Copy
    Cells(1, 1).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    ActiveWorkbook.SaveAs Filename:=Format(Now(), "mm.dd.yyyy-hhnn"), FileFormat:=xlCSV
    ActiveSheet.Name = "AE_UPLOAD"
    Range("A1").Select

Thanks for pointing me in the right direction!