Forum Discussion

calof1's avatar
calof1
Iron Contributor
May 04, 2021
Solved

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

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 spre...
BI & Data Analysis
developer
excel
Excel on Mac
Formulas and Functions
Macros and VBA
power bi
  • HansVogelaar's avatar
    HansVogelaar
    May 04, 2021

    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

calof1's avatar
calof1
Iron Contributor
May 05, 2021
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

HansVogelaar's avatar
HansVogelaar
MVP
May 05, 2021

calof1 

Does it help if you change

 

sourceStartRow = 2

 

to

 

sourceStartRow = 3

  • calof1's avatar
    calof1
    Iron Contributor
    May 06, 2021

    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.

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 07, 2021

      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

       

Resources