Forum Discussion

redblooda's avatar
redblooda
Copper Contributor
Nov 13, 2020

Saving as PDF depending on Cell Value

Hi, I'm pretty new to the macros. So any help would be great.

What I want to do is;
1- I need Cell "A2" in sheet "B3" to change to the values between "B7:B169" in sheet "VerTab"
2- Then save sheet B3 as pdf with the name as the value in "A2"in sheet "B3".

To explain simply, I want to change Cell A2 Value in Sheet "B3" to Cell B7 Value in Sheet "VerTab" and print sheet to PDF with the file name as Cell A2 Value.
Then do the same but for Cell B8 in Sheet "VerTab"
Then do the same but for Cell B9 in Sheet "VerTab"
Then do the same but for Cell B10 in Sheet "VerTab"
and so on till the end in Column B in Sheet "VerTab"

Right now code does 2nd part. but 1st part is only changing between what is written in "VList = Array("C.AGM2EL 71 2", "AGM2EL 80 2a")"

 

Sub SaveAsPDF()

    Dim i As Integer
    Dim VList As Variant

    VList = Array("C.AGM2EL 71 2", "AGM2EL 80 2a")

    For i = LBound(VList) To UBound(VList)
        Range("A2") = VList(i)
        Dim rngRange        As Range

Dim fName As String
With ActiveSheet
    fName = "C:\Users\Redblood\Documents\datasheet\" & Range("A2").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
Next
End Sub

 

Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 13, 2020

    redblooda 

    Like this:

    Sub SaveAsPDF()
    Dim i As Long
    Dim VList As Variant
    Dim fName As String

    VList = Worksheets("VerTab").Range("B7:B169").Value
    For i = LBound(VList) To UBound(VList)
        With Worksheets("B3")
            .Range("A2") = VList(i, 1)
            fName = "C:\Users\Redblood\Documents\datasheet\" & VList(i, 1)
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next i
End Sub

Resources