BDM AT WCS
New Member
- Joined
- Dec 17, 2018
- Messages
- 7
I'm implementing a 'live' and self-service reporting regime. I want managers to be able to access their own area's live data and take a snapshot of it at any point in time by saving selected sheets to a pdf file on their respective Desktops. The macro I've put together runs without errors - but it doesn't save the pdf to the desktop. Can anyone see what's wrong here? The "Worksheets("inputs").Range("$D$3").Value" is picking up today's date in yymmdd format such that the multiple snapshot files they'll end up with can be easily sorted and found in Windows Explorer. I think it's here where the problem lies - it seemed to be working and saving to the Desktop when I had the filename starting with a text value of "Commercial Papers" and then appending the date to the filename - but this way around, something's gone wrong. Can you spot it?
VBA Code:
Sub Save_As_PDF_To_Desktop()
Dim xDesktop As String
Dim sheetArray As Variant
xDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
sheetArray = Array("action sheet", "agenda", "my sector commercial summary", "Paper B Live", "Paper B Productive", "Paper C Income Nat", "Paper C Income Inter", "Paper C Income NMC", "Income By Sector", "gap_analysis", "my Gap", "Paper_D_Y-on-Y", "FWDF Summary")
For Each ws In ActiveWorkbook.Worksheets
ws.Columns.AutoFit
Next ws
Sheets(sheetArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=xDesktop & Worksheets("inputs").Range("$D$3").Value & " Commercial Papers " & ".pdf"
End Sub