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

budfox310

New Member
Joined
Jan 15, 2021
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

bobaol

Board Regular
Joined
Jun 3, 2002
Messages
117
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

Board Regular
Joined
Aug 11, 2020
Messages
56
Office Version
  1. 365
Platform
  1. Windows
Here is a solution for your other site posting:

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,237
Messages
5,600,475
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top