Referencing an excel file with previous month as name to paste data into it with VBA-CodePudding

I'm looking for the VBA code to reference an excel file which has the previous months as it's name.

I have tried the below code, which creates the file from a template & saves it with the previous months name no problem. I then try to reference this file in order to paste some values into it, but I keep getting Runtime Error 9: Subscript out of range.

Workbooks.Open Filename:="https://X.sharepoint.com/GROUP/Whiteboard/Malton New Month Template.xlsx?web=1"

ActiveWorkbook.SaveAs Filename:="https://X.sharepoint.com/GROUP/Whiteboard/2022/Malton/Malton " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm yyyy"), FileFormat:=51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Workbooks("WHITEBOARD project.xlsm").Worksheets("Malton Weekly Input").Range("A5:R404").Copy

Workbooks("Malton " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm yyyy" & "xlsx")).Worksheets("Data").Range("AC5:AT404").PasteSpecial Paste:=xlPasteValues

Any help in pointing me in the right direction would be greatly appreciated

Thanks in advance

Allan

CodePudding user response：

Your file naming is not consistent, but if you get a reference to the file when you first open it, you don't need to refer to it by name.

Sub tester()
    
    Const ROOT As String = "https://X.sharepoint.com/GROUP/Whiteboard/"
    
    Dim prevMnth As String, wb As Workbook
    
    'get a reference when you open the file...
    Set wb = Workbooks.Open(Filename:=ROOT & "Malton New Month Template.xlsx?web=1")
    
    prevMnth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm yyyy")
    
    wb.SaveAs Filename:=ROOT & "2022/Malton/Malton " & prevMnth & ".xlsx", _
              FileFormat:=51, Password:="", WriteResPassword:="", _
              ReadOnlyRecommended:=False, CreateBackup:=False

    Workbooks("WHITEBOARD project.xlsm").Worksheets("Malton Weekly Input").Range("A5:R404").Copy
    
    wb.Worksheets("Data").Range("AC5").PasteSpecial Paste:=xlPasteValues
    
End Sub