Help w/simple two step macro - Excel 2013

bman84

New Member
Joined
Oct 6, 2014
Messages
12
Hey there, I'm using Excel 2013, and hoping you can help me piece together the code to create a simple macro. I have an Excel spreadsheet that acts as a simple statement generator by populating a number of cells based on a VLOOKUP against a master list of account numbers.

The user enters the 4-digit account number, and the VLOOKUP formulas on a separate tab of the workbook bring back the required values (ie. Customer Name, Address, Balance, etc) from the master list.

Rather than have the user spend time entering an account number, and manually saving a PDF, I'm hoping to create a macro that does those steps for them. Desired process would look like this:

1) User pastes list of account numbers in COLUMN L on 'Entry' sheet
2) User enters a desired file path in cell A5 (location where PDF's will be saved)
3) User clicks a button to start the macro
4) Macro reads first account number in COLUMN L and writes it to cell H7
5) Macro moves to separate sheet called 'Renewal Letter' and saves the sheet as a PDF, with the same filename as the text in cell K6
6) Macro moves back to 'Entry' and writes the next account number from COLUMN L to cell H7
....and the loop of steps 5) and 6) continues until the entire list in COLUMN L has been completed


Thanks in advance for any help!
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
Your request is neither simple nor two steps :LOL:.

It certainly is doable, though, although I want to clarify a few things.
Can you confirm that
1. When you reference cell H7 you mean H7 on the 'Entry' sheet
2. When you reference cell A5 you also mean A5 on the 'Entry' sheet
3. When you reference column L you also mean on the 'Entry' sheet and also, what cell would the values start at? L2? with L1 being a header?
4. When you reference cell K6 you mean K6 on the 'Renewal Letter' Sheet (and that this is a formula that automatically updates)

Once I have these answers I think I (or someone else) can attempt a 'simple two step macro'. :)
 

bman84

New Member
Joined
Oct 6, 2014
Messages
12
Sorry, seemed a lot simpler in my head haha. The answer to all of your questions is YES. Thanks!

Your request is neither simple nor two steps :LOL:.

It certainly is doable, though, although I want to clarify a few things.
Can you confirm that
1. When you reference cell H7 you mean H7 on the 'Entry' sheet
2. When you reference cell A5 you also mean A5 on the 'Entry' sheet
3. When you reference column L you also mean on the 'Entry' sheet and also, what cell would the values start at? L2? with L1 being a header?
4. When you reference cell K6 you mean K6 on the 'Renewal Letter' Sheet (and that this is a formula that automatically updates)

Once I have these answers I think I (or someone else) can attempt a 'simple two step macro'. :)
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
After minimal testing, this appears to work.
I recommend starting with a small list to verify execution first.

Paste the following code into a Standard Module
Code:
Sub Export2PDF()
Dim LookupArr As Variant
Dim i As Long
Dim filePath As String, fileName As String
Dim wsEntry As Worksheet, wsPrint As Worksheet
Dim AutoOpen As Boolean
PDFAutoOpen = False    'change this to TRUE if you want the PDF documents to open after being saved
Set wsEntry = ThisWorkbook.Sheets("Entry")
Set wsPrint = ThisWorkbook.Sheets("Renewal Letter")
    LookupArr = Application.Transpose(wsEntry.Range("L2:L" & wsEntry.Range("L" & Rows.Count).End(xlUp).Row).Value)
    filePath = wsEntry.Range("A5").Value
    If Right(filePath, 1) <> "\" Then filePath = filePath & "\"
    
    For i = LBound(LookupArr) To UBound(LookupArr)
        wsEntry.Range("H7").Value = LookupArr(i)
        Application.Calculate   'ensure that all formulas are updated
        fileName = wsPrint.Range("K6").Value
        wsPrint.ExportAsFixedFormat xlTypePDF, filePath & fileName, , , False, , , PDFAutoOpen
        DoEvents    'let's Adobe finish with the file
    Next i
    
Set wsEntry = Nothing
Set wsPrint = Nothing
End Sub
In the end, it was simpler than I expected so your title wasn't so incorrect. ;)
 

bman84

New Member
Joined
Oct 6, 2014
Messages
12
Thanks! Giving it a try now and getting 'Run-time error 1004' with the following line:
wsPrint.ExportAsFixedFormat xlTypePDF, filePath & fileName, , , False, , , PDFAutoOpen


After minimal testing, this appears to work.
I recommend starting with a small list to verify execution first.

Paste the following code into a Standard Module
Code:
Sub Export2PDF()
Dim LookupArr As Variant
Dim i As Long
Dim filePath As String, fileName As String
Dim wsEntry As Worksheet, wsPrint As Worksheet
Dim AutoOpen As Boolean
PDFAutoOpen = False    'change this to TRUE if you want the PDF documents to open after being saved
Set wsEntry = ThisWorkbook.Sheets("Entry")
Set wsPrint = ThisWorkbook.Sheets("Renewal Letter")
    LookupArr = Application.Transpose(wsEntry.Range("L2:L" & wsEntry.Range("L" & Rows.Count).End(xlUp).Row).Value)
    filePath = wsEntry.Range("A5").Value
    If Right(filePath, 1) <> "\" Then filePath = filePath & "\"
    
    For i = LBound(LookupArr) To UBound(LookupArr)
        wsEntry.Range("H7").Value = LookupArr(i)
        Application.Calculate   'ensure that all formulas are updated
        fileName = wsPrint.Range("K6").Value
        wsPrint.ExportAsFixedFormat xlTypePDF, filePath & fileName, , , False, , , PDFAutoOpen
        DoEvents    'let's Adobe finish with the file
    Next i
    
Set wsEntry = Nothing
Set wsPrint = Nothing
End Sub
In the end, it was simpler than I expected so your title wasn't so incorrect. ;)
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
I forgot the sheet being exported needs to be active (apparently)

Try this amendment. Also, I cleaned up the ExportAsFixedFormat method's arguments so it is more clear what they are for.

Rich (BB code):
Sub Export2PDF()
Dim LookupArr As Variant
Dim i As Long
Dim filePath As String, fileName As String
Dim wsEntry As Worksheet, wsPrint As Worksheet
Dim AutoOpen As Boolean
PDFAutoOpen = False    'change this to TRUE if you want the PDF documents to open after being saved
Set wsEntry = ThisWorkbook.Sheets("Entry")
Set wsPrint = ThisWorkbook.Sheets("Renewal Letter")
    LookupArr = Application.Transpose(wsEntry.Range("L2:L" & wsEntry.Range("L" & Rows.Count).End(xlUp).Row).Value)
    filePath = wsEntry.Range("A5").Value
    If Right(filePath, 1) <> "\" Then filePath = filePath & "\"
    
    For i = LBound(LookupArr) To UBound(LookupArr)
        wsEntry.Range("H7").Value = LookupArr(i)
        Application.Calculate   'ensure that all formulas are updated
        fileName = wsPrint.Range("K6").Value
        wsPrint.Activate
        wsPrint.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath & fileName, IgnorePrintAreas:=False, OpenAfterPublish:=PDFAutoOpen
        DoEvents    'let's Adobe finish with the file
    Next i
    
Set wsEntry = Nothing
Set wsPrint = Nothing
End Sub
 

bman84

New Member
Joined
Oct 6, 2014
Messages
12
Thanks, but still getting the same error.

'Run-time error 1004' with the following line:
wsPrint.ExportAsFixedFormat xlTypePDF, filePath & fileName, , , False, , , PDFAutoOpen
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
Is it actually created the file?

If not, try putting this line before the ExportAsFixedFormat line to give you an idea of what/where it is trying to save to...

Msgbox filePath & fileName

Let me know what pops up in the message.
 

bman84

New Member
Joined
Oct 6, 2014
Messages
12
Popup box displays 'P:\Desktop\renewal\1110'. It did not actually create any files

Is it actually created the file?

If not, try putting this line before the ExportAsFixedFormat line to give you an idea of what/where it is trying to save to...

Msgbox filePath & fileName

Let me know what pops up in the message.
 
Last edited:

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
That means that A5 on the Entry sheet (where the path was supposed to be entered) is blank.

I updated my code to be more accomodating and prompt for the path if it is not available in A5.
I also placed a little more error conditioning around the ExportAsFixedFormat line so THAT line won't error out anymore.

Let me know if you are still having problems.

Code:
Sub Export2PDF()
Dim LookupArr As Variant
Dim i As Long
Dim filePath As String, fileName As String
Dim wsEntry As Worksheet, wsPrint As Worksheet
Dim AutoOpen As Boolean
PDFAutoOpen = False    'change this to TRUE if you want the PDF documents to open after being saved
Set wsEntry = ThisWorkbook.Sheets("Entry")
Set wsPrint = ThisWorkbook.Sheets("Renewal Letter")
    LookupArr = Application.Transpose(wsEntry.Range("L2:L" & wsEntry.Range("L" & Rows.Count).End(xlUp).Row).Value)
    filePath = wsEntry.Range("A5").Value
    
    If filePath = "" Then
        Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
        With fldr
            .Title = "Select a Folder"
            .AllowMultiSelect = False
            .InitialFileName = ThisWorkbook.Path
            If .Show <> -1 Then
                Exit Sub    'user hit Cancel
            End If
            filePath = .SelectedItems(1)
        End With
    End If
    If Right(filePath, 1) <> "\" Then filePath = filePath & "\"
    
    For i = LBound(LookupArr) To UBound(LookupArr)
        wsEntry.Range("H7").Value = LookupArr(i)
        Application.Calculate   'ensure that all formulas are updated
        fileName = wsPrint.Range("K6").Value
        wsPrint.Activate
        On Error Resume Next
        wsPrint.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath & fileName, IgnorePrintAreas:=False, OpenAfterPublish:=PDFAutoOpen
        On Error GoTo 0
        If Dir(filePath & fileName & "*") = "" Then
            MsgBox "Could NOT save " & filePath & fileName, vbCritical + vbOKOnly, "Error Exporting PDF"
        End If
        DoEvents    'let's Adobe finish with the file
    Next i
    
Set wsEntry = Nothing
Set wsPrint = Nothing
End Sub
 

Forum statistics

Threads
1,081,753
Messages
5,361,097
Members
400,613
Latest member
Markdc123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top