SOLVED

Help to update Macro to save CSV file with month and year in file name

%3CLINGO-SUB%20id%3D%22lingo-sub-2319771%22%20slang%3D%22en-US%22%3EHelp%20to%20update%20Macro%20to%20save%20CSV%20file%20with%20month%20and%20year%20in%20file%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319771%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20month%20i%20have%20some%20data%20i%20need%20to%20upload%2C%20so%20save%20this%20as%20a%20CSV%20using%20a%20macro.%20I%20am%20hoping%20to%20have%20the%20file%20name%20updated%20to%20match%20the%20Month%20and%20year%20listed%20in%20cells%20C2%20%26amp%3B%20C3%20of%20my%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20has%20any%20suggestions%20on%20how%20to%20assist%20with%20this%2C%20it%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECallum%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2319771%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2324226%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20update%20Macro%20to%20save%20CSV%20file%20with%20month%20and%20year%20in%20file%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324226%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20a%20lot%20for%20your%20help%20with%20this%2C%20it%20works%20exactly%20as%20I%20was%20hoping%20for.%20This%20is%20very%20much%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20also%20noticed%20the%20macro%20includes%20the%20entire%20sheet.%20If%20I%20wish%20for%20it%20start%20from%20Row%202%20(to%20exclude%20the%20line%20showing%20the%20file%20path)%2C%20would%20this%20possible%3F%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hi All,

 

Each month i have some data i need to upload, so save this as a CSV using a macro. I am hoping to have the file name updated to match the Month and year listed in cells C2 & C3 of my spreadsheet.

 

If anyone has any suggestions on how to assist with this, it would be greatly appreciated.

 

Kind regards,

 

Callum

5 Replies
best response confirmed by calof1 (Regular Contributor)
Solution

@calof1 

Change the line

 

destinationSheet.SaveAs Filename:=saveToLocation & "\csvfile.csv", FileFormat:=xlCSV, CreateBackup:=False

 

to

 

destinationSheet.SaveAs Filename:=saveToLocation & "\" & Format(Date, "mmmm yyyy") & ".csv", FileFormat:=xlCSV, CreateBackup:=False

Hi Hans,

Thanks a lot for your help with this, it works exactly as I was hoping for. This is very much appreciated.

I have also noticed the macro includes the entire sheet. If I wish for it start from Row 2 (to exclude the line showing the file path), would this possible?

Many thanks

@calof1 

Does it help if you change

 

sourceStartRow = 2

 

to

 

sourceStartRow = 3

Hi @hans,

Thanks for your reply.

I have tried to update this, however then i get the below error when i run the macro.
Unfortunately i am not sure about this error.

Thanks for your help, much appreciated.

@calof1 

Change the lines

 

'Copying data to new sheet
destinationSheet.Range(destinationSheet.Cells(1, sourceStartCol), destinationSheet.Cells(sourceEndRow - sourceStartRow, sourceEndCol)).Value = sourceSheet.Range(sourceSheet.Cells(sourceStartRow, sourceStartCol), sourceSheet.Cells(sourceEndRow, sourceEndCol)).Value

 

to

 

'Copying data to new sheet
destinationSheet.Range(destinationSheet.Cells(1, sourceStartCol), destinationSheet.Cells(sourceEndRow - sourceStartRow + 1, sourceEndCol)).Value = sourceSheet.Range(sourceSheet.Cells(sourceStartRow, sourceStartCol), sourceSheet.Cells(sourceEndRow, sourceEndCol)).Value