[skifozoa]

I'm trying to build a printable weekly planner with 1 A4 per week.

I have succesfully built a template in excel that suits my needs and a macro that loops through the next few weeks, in this case 10, and automatically fills the date fields. Before moving to the next week, this macro fills the date fields of the template with the correct days, months and years and prints the filled template to pdf. After this, it overwrites the template with the correct dates for the next week and again saves to pdf, a different one each iteration.

Unfortunately, but logically, this procedure gives me multiple single-page pdf files. One pdf file for each week to be exact. For obvious reasons, I would rather have a single pdf file with multiple pages.

Does VBA allow me to adjust my code so each iteration the new output is appended to an already existing pdf file instead of having to create a new one each iteration?

Thanks for any help you could offer !

ps: below you find the macro, I think it's rather self-explanatory, but in case something isn't clear after all, just let me know.

Sub Build()

'PREAMBLE
weeks = 10

'CALCULATE THIS WEEKS' MONDAY
ThisMonday = Date - Weekday(Date) + 2

'ITERATE PROGRAM
iterations = weeks - 1
For i = 0 To iterations
    CurrentMonday = ThisMonday + i * 7
    CurrentSunday = CurrentMonday + 6
    
    If Month(CurrentMonday) = 12 And Month(CurrentMonday) <> Month(CurrentSunday) Then
        Heading = MonthName(Month(CurrentMonday)) & " " & Year(CurrentMonday) & " / " & MonthName(Month(CurrentSunday)) & " " & Year(CurrentSunday)
    ElseIf Month(CurrentMonday) <> Month(CurrentSunday) Then
        Heading = MonthName(Month(CurrentMonday)) & " / " & MonthName(Month(CurrentSunday)) & " " & Year(CurrentMonday)
    Else
        Heading = MonthName(Month(CurrentMonday)) & " " & Year(CurrentMonday)
    End If
    
    ThisWorkbook.Worksheets("Week").Range("mon") = Day(CurrentMonday)
    ThisWorkbook.Worksheets("Week").Range("tue") = Day(CurrentMonday + 1)
    ThisWorkbook.Worksheets("Week").Range("wed") = Day(CurrentMonday + 2)
    ThisWorkbook.Worksheets("Week").Range("thu") = Day(CurrentMonday + 3)
    ThisWorkbook.Worksheets("Week").Range("fri") = Day(CurrentMonday + 4)
    ThisWorkbook.Worksheets("Week").Range("sat") = Day(CurrentMonday + 5)
    ThisWorkbook.Worksheets("Week").Range("sun") = Day(CurrentMonday + 6)
    ThisWorkbook.Worksheets("Week").Range("month") = StrConv(Heading, vbUpperCase)
    
    ThisWorkbook.Worksheets("Week").Activate
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=i & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    
Next i



End Sub

[maj3091]

I'm not sure it will let you append to PDF, but you could try writing the data for each page to a separate worksheet, then save the whole workbook as a PDF, which I assume would give you a multiple page document.

Bear in mind I'm theorising here....it's not something I've ever done before, but should be fairly simple for you to test.