Loop Through Column Individually/Save to PDF

[mywifejustleftme]

I was trying to make a macro that loops through a range of columns by selecting each one individually and then saves it to a PDF after each selection.

To give context, it's a report in-which I select a number from a column (store #), the report will then populate according to that store number. So I need a way to automatically cycle through these stores and save the individual store report without indiviually selecting all 300 stores one by one.

If someone could give me an idea on where to begin! So far, I started with recording a macro and then working on a loop to go through the whole list automatically, but I could only get it to loop through the cells I already indivually typed in (obviously I can't type in 300 stores).

 

[Kenneth Hobson]

Welcome to the forum!

Do you have a pseudo form where you select a value from a dropdown which then fills the "form"? If so, what is the dropdown sheet and cell address?

If the sheet to print to pdf is not the sheet with the dropdown, which is it?

 

[mywifejustleftme]

Pleasure to be here Kenneth!

There is no drop down. Rather, we have a cell that says "store number" and to the right of that you type in the number which will then populate the whole sheet accordingly. For print to pdf, is there a way to save just the sheet you're currently on (where you put in store number) and the next 3 (it has over 50 tabs probably)?

 

[Kenneth Hobson]

Before we get into the details too much, lets look at the total goal. Are you wanting to print just the one sheet or include 3 more? That would be 4 pages to print to the pdf file? Of course there could be more than one page per worksheet/tab depending on the usedrange or set print range.

Looping to change a cell say Worksheets("Master").Range("A1").Value from 1 to 300 stores is easy. If there is a list of store IDs or Names to fill rather than 1 to 300, that can be done too.

 

[mywifejustleftme]

Exactly, it would be 4 total! Yes, there is a list of store ID's (which totals to around 300, but I'm estimating).

 

[Kenneth Hobson]

1. Name of worksheet and cell address of ID's to change.
2. Name of worksheet and cell addresses beginning to end for all ID's. If no blanks, the first ID say Worksheets("Store_IDs").Range("A2") for first is needed. The last can be determined dynamically.
3. Name of other 3 worksheets. Note: worksheet's index order governs which is printed to pdf first.

 

[mywifejustleftme]

1. Ids to change? (I may be confused)
2. The worksheet with store Ids is called "storeref" and they start at "A1" and end at "A325"
3. the other worksheets are sheet2, sheet3, sheet4 (I wanted to get this macro done before naming everything)

 

[Kenneth Hobson]

Obviously, change pdfPath and the sheet names to suit.

Sub Main()
  Dim c As Range, pdfPath As String
  
  pdfPath = Environ("temp") & "\"
  
  Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
  For Each c In Worksheets("storef").Range("A1", Worksheets("storef").Range("A1").End(xlDown))
    Worksheets("Sheet1").Range("A1").Value = c.Value
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
    PublishToPDF pdfPath & c.Value & ".pdf", ActiveSheet
    Worksheets("Sheet1").Select
  Next c
End Sub


Function PublishToPDF(fName As String, o As Object, _
  Optional tfGetFilename As Boolean = False) As String
  Dim rc As Variant
  
  rc = fName
  If tfGetFilename Then
    rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
    If rc = "" Then Exit Function
  End If
  
  o.ExportAsFixedFormat Type:=xlTypePDF, Filename:=rc _
  , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
  :=False, OpenAfterPublish:=False
  
  PublishToPDF = rc
End Function