I am using the macro from this answer, which allows you to split up a file based on the values of a specified column. I adjusted it to add
Dim sMonthYear As String ' Month & Year of report
sMonthYear = Application.InputBox("Enter the Month and Year of the report", "Enter text", , , , , , 2)
where the user enters a string, and I changed the SaveAs so it looks like
ash.SaveAs sFilePath + "Split" + sSectionName + "" + sMonthYear + "" + osh.Name, fileFormat
in order to create a file path that looks like: SplitGroup 1October 2019Report.xlsx. I also added sMonthYear to the commands on line 69, 87, and 114 to ensure the variable was passed to the new worksheet and saved.
However, when I run it, it gives me this error:
Run-time error ‘1004’:
Microsoft Excel cannot access the file …SplitGroup 1October 2019CBAE2000′. There are several possible reasons:
I’m not sure what I’m doing wrong. When I remove the sMonthYear bit and the backslashes in the quotes, it works fine. When I click ‘debug’, it takes me to the ash.SaveAs line.
