VBA - Change Cell Value to Next in a List, Save Sheet as PDF, Repeat

[budfox310]

Hello - I need a macro that saves a sheet to PDF, then changes a cell value to the next name on a list to run the analysis for that name, then prints the sheet again to a separate PDF, and repeats until it has run through all the names. I've been looking but haven't found exactly what I'm looking for.

For example, let's say I have 2 sheets, 'Output' and 'Data'. The Data tab contains sales and other info for 10 locations. The Output pulls in data from the Data tab for whichever location name is entered into cell A1 and then runs an analysis. I need a macro that generates a PDF of the Output sheet, then changes cell A1 for the next location and saves another PDF, and repeats until a report has been saved for each location.

Filepath: Cell A2 (Output)
Filename: Cell A3 (Output)
List of Locations: K2:K (Output)

Thank you!!

Also posted here VBA - Change Cell Value to Next in a List, Save Sheet as PDF, Repeat

 

[bobaol]

try this. the example file is here. '''''' Print_Data_Output___.xlsm
running macro1 will run macros2,3, and 4

Sub macro1()
Application.Run "Macro2_CountA_"
Application.Run "Macro3__Print_Data_Output___"
Application.Run "Macro4__Print_Data_Output___"
End Sub

Sub Macro2_CountA_()
'use Counta to find the number of time to repeat the print job
Sheets("Print").Select
Application.Goto Reference:="R11C2"
ActiveCell.FormulaR1C1 = "''counta for number of times to repeat the macro"
Application.Goto Reference:="R12C2"
Selection.FormulaR1C1 = "=COUNTA(Data!R[-11]C[-1]:R[1048563]C[-1])"
End Sub


Sub Macro3__Print_Data_Output___()
''' assumes your data start in cell A1
''' 1. go to sheet Data, go to cell A1
Sheets("Data").Select
Application.Goto Reference:="R1C1"

End Sub


Sub Macro4__Print_Data_Output___()
''' for repeating, from cell B12
For i = 1 To Range("Print!B12")

Sheets("Data").Select
''' 2. copy the active cell in the sheet Data
Selection.Copy

''' 3. go down to the next cell, so it is selected and copied for your next sales_department
ActiveCell.Offset(1, 0).Range("A1").Select

''' 4. go to Sheet Output, Go to cell A1
Sheets("Output").Select
Application.Goto Reference:="R1C1"

''' 5. paste as values in cell A1
Selection.PasteSpecial Paste:=xlPasteValues

'' print to pdf, thanks to MrExcel... '''VBA to print pdf to folder
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Print_Data_Output" & Range("A1") & ".pdf", Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next
End Sub

 

[ManiacB]

Here is a solution for your other site posting:

Sub crtpdfcnhgcel()

Dim x As Range
Dim Rng As Range
Dim last As Long
Dim sht, dat As Worksheet
Dim strPath, strFName As String

Application.ScreenUpdating = False
Set sht = Sheets("Output")
Set dat = Sheets("Data")

last = dat.Cells(Rows.Count, "A").End(xlUp).Row

dat.Range("A1:A" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("BB1"), Unique:=True

    For Each x In Range([BB2], Cells(Rows.Count, "BB").End(xlUp))
        sht.[A1].Value = x
        
        strPath = "C:\Users\Documents\Labor Compliance\" 'ThisWorkbook.Path & "\"
        strFName = sht.[A3].Value & ".pdf"
 
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strPath & strFName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next x
    
    ' Remove data from column BB
    Range([BB1], Cells(Rows.Count, "BB").End(xlUp)) = ""
    
Application.ScreenUpdating = True
    
End Sub