VBA Loop: Enter Value in a Cell then Save a Subset of Sheets as pdf

[itord1973]

Hi guys. This is my first post.

I created an eternal calendar in Excel. I need to come up with a loop that would feed one of the cells with a specific starting year, let Excel do its thing, and then print the results as pdf file for each year. Leap years and common years would print from different sheets. For example, a common year would print 'Calendar_0', 'Calendar_1', and 'Calendar_3' but a leap year would print 'Calendar_0', 'Calendar_2', and 'Calendar_4'. The range of years is from years 1900 through 5100. I tried recording a macro and modifying it but I got nowhere.

I would really appreciate your help.

Ivan

 

[John_w]

Which is the exact cell where the starting year should be put? In the macro below, this cell is A2 on the sheet named "Year". The macro then loops though the years 1900 to 1908, saving the specific sheets as the year PDF (1900.pdf, 1901.pdf, etc) in the specified folder.

Public Sub Create_Calendar_PDFs()

    Dim saveInFolder As String
    Dim yearCell As Range
    Dim y As Integer
    
    saveInFolder = "C:\Temp\"  'folder path where PDFs are created
    
    Set yearCell = Worksheets("Year").Range("A2")
    
    For y = 1900 To 1908
        yearCell.Value = y
        If IsLeapYear(y) Then
            Worksheets(Array("Calendar_0", "Calendar_2", "Calendar_4")).Select
        Else
            Worksheets(Array("Calendar_0", "Calendar_1", "Calendar_3")).Select
        End If
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & y & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
    
    yearCell.Worksheet.Select
    
End Sub


Private Function IsLeapYear(y As Integer) As Boolean
    IsLeapYear = Month(DateSerial(y, 2, 29)) = 2
End Function

 

[itord1973]

John_W, you are an absolute genius! Thank you! It's working perfectly!

Regards,
Ivan