Hi,
So I managed to get a large portion of the code to work except one item. The code changes a cell with a date which is the last Friday, Calculates the workbook, then selects certain sheets and then exports them to PDF. I have all of that working except that it will not wait until all the data is updated to export to PDF so I get a bunch of "#N/A Requesting Data..." on the exported PDF. Not sure what in the code isn't trigering. Thanks
Private Sub Workbook_Open()
Dim time1, time2
Dim MyDay As Integer
Dim LastFriday, LastMonday
Dim MyFileName As String
MyDay = Weekday(Date)
LastFriday = Date - (MyDay + 1)
'
Sheets("Sheet1").Select
ActiveSheet.Range("$C$4") = LastFriday
Application.Calculate
Call Application.OnTime(Now + TimeValue("00:00:01"), "ProcessData")
ActiveWorkbook.Save
ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Filepath" & "\" & "filename." & WorksheetFunction.Text(LastFriday, "yyyy.mm.dd"), Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Application.Quit
End Sub
Private Sub ProcessData()
Dim c As Range
c = ThisWorkbook.Sheets("Summary Sheet").Activate
ActiveSheet.Range("A1:L145").Select
For Each c In Selection.Cells
If c.Value = "#N/A Requesting Data..." Then
Call Application.OnTime(Now + TimeValue("00:00:01"), "ProcessData")
Exit Sub
End If
Next c
Bookmarks