PDF creation of different sheet based on column value.

[mummbles74]

Ok something I haven't attempted before.

I have a spread sheet that has sheet containing all of the information for different electrical loops. (sheet name "DATA")
I started with a signal template on a separate sheet ("Cable Schematic") and I use other formulas directly in the cells on that sheet to 'transfer' over the information from the DATA sheet based on the tag number selected in a certain cell (AC1)
The code below cycles through each row on the DATA sheet, on the CABLE SCHEMATIC template and creates a PDF for each row.

Row = 2
Do Until IsEmpty(Worksheets("Data").Cells(Row, 1))
Sheets("Cable schematic").Range("AC1") = Worksheets("Data").Cells(Row, 1)
Sheets("Cable schematic").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Worksheets("Data").Cells(Row, 7) & "-" & Worksheets("Data").Cells(Row, 1)
Row = Row + 1
Loop

the above code seem to work perfectly, puts the created PDF's in the same folder as the excel sheet and does exactly what is required.

I now have several other templates across other sheets that are different templates that reflect the different designs of circuits. On the DATA tab, column BN states what template to use. The different templates are called the following:

Cable schematic

Cable + JB schematic

Cable + Plug schematic

Cable + Plug No Probe

What I would like to do is still cycle through the ROWs on the DATA tab but this time based on the value in BN select a different template type. I assumed that it would be a case of running the above code several times basing it on the value in BN but I can not see any other examples that explain how to do this.

Any help would be greatly appreciated.

 

[John_w]

Try this macro:

Public Sub Create_PDFs()

    Dim Row As Long
    
    Row = 2
    With Worksheets("Data")
        Do Until IsEmpty(.Cells(Row, "A").Value)
            Worksheets(.Cells(Row, "BN").Value).Range("AC1") = .Cells(Row, "A").Value
            Worksheets(.Cells(Row, "BN").Value).ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & .Cells(Row, "G").Value & "-" & .Cells(Row, "A").Value
            Row = Row + 1
        Loop
    End With
    
    MsgBox "Done"
    
End Sub

 

[mummbles74]

Try this macro:

Public Sub Create_PDFs()

    Dim Row As Long
  
    Row = 2
    With Worksheets("Data")
        Do Until IsEmpty(.Cells(Row, "A").Value)
            Worksheets(.Cells(Row, "BN").Value).Range("AC1") = .Cells(Row, "A").Value
            Worksheets(.Cells(Row, "BN").Value).ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & .Cells(Row, "G").Value & "-" & .Cells(Row, "A").Value
            Row = Row + 1
        Loop
    End With
  
    MsgBox "Done"
  
End Sub

That is amazing thank you.