The context before my question: We have an excel sheet we open and modify every morning on a meeting. We then save that as a pdf with the name of the day in the title, next to the excel. All of this i was able to automate to a command button using this:
Sub SaveActiveWorkbookAsPDF()
Dim saveLocation As String
saveLocation = ThisWorkbook.Path & "" & "Daily file_w" & Format(Date, "ww") & _
"_" & Format(Date, "dddd")
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
End Sub
And here is what i got stuck on: We then copy this pdf file to a different folder (for other departments), and i would like to automate this to the button aswell. My problem is, we have these excels in different folders for every week, titled "w02" and so on with numbers. The alternative location is the same weekly way, but the folder titles are formatted like "2502" (- as in 2025, second week). The folders are pre-created and titled, so i have no wiggle-room on that.
The file name has an automated week number in it, and within the sheet theres also a cell that tells the number of the week. Could i maybe use these so the VBA finds the folders? Thank you!
EDIT: Original path (where the excel is too) I:\01-05\Data\Daily\2025\w2 (number based on current week) The other path (to where we copy the pdf) I:\04-80-PUBLIC\Daily Report\2025\2502 (again, last number based on current week) (sorry for the trainwreck post, trying to get my thoughts together haha)
Like this maybe:
Sub SaveActiveWorkbookAsPDF()
Dim saveLocation As String, saveName As String, weekFolder As String
saveLocation = ThisWorkbook.Path & "\"
saveName = "Daily file_w" & Format(Date, "ww") & "_" & Format(Date, "dddd")
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation & saveName
weekFolder = "I:\04-80-PUBLIC\Daily Report\" & Year(Date) & _
"\" & Format(Date, "yy") & Format(Format(Date, "ww"), "00") & "\"
FileCopy saveLocation & saveName, weekFolder & saveName
End Sub