0

I am new to VBA and this website for inquiring a question. I am trying to have VBA automatically save 1 row of items (i.e. cells A1 and B1) to pdf document with it capturing cells A1 as the documents filename.pdf to a folder I have choose on my desktop. There are roughly 1800 rows, and therefore would allow for 1800 different pdfs with different names as the names would change from A1-A1800.

I have a code that automatically saves to pdf, however it is user defined for the naming and asks you what you would like to name for every file shown below, keep in mind this is setup for small file to test on before moving to 1800 items:

Sub TESTLOOPPRINT()

ActiveSheet.PageSetup.PrintArea = "$1:$2"
Rows("1:2").EntireRow.Hidden = True

For i = 1 to 2

Rows(i).EntireRow.Hidden = False
ChDir "C:\Users\ABB6632\Test\"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows(i).EntireRow.Hidden = True

Next i
Rows("1:2").EntireRow.Hidden = False

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Andrew Svec
  • 1
  • 1
    Have a look at https://stackoverflow.com/questions/27219784/vba-print-to-pdf-and-save-with-automatic-file-name which uses a different method to 'print' but solves your filename problem. You'll probably need to create a loop that creates a new workbook containing just the lines you want, prints it, closes it and then moves to the next one. – CLR Jul 12 '21 at 14:09
  • Thank you for the option, took some editting on my end, but believe I was able to solve it with the funciton and sub. For future reference if anyone is needing, copied both files from top comment on that link, edited only the sub to the following which allowed user to edit print area and print 1 row to pdf, with file name being in row a1-a1800: – Andrew Svec Jul 12 '21 at 16:47
  • Sub Testing() ActiveSheet.PageSetup.PrintArea = "$1:$3" Rows("1:3").EntireRow.Hidden = True For i = 1 To 3 'loop for total rows 1-x set by user Rows(i).EntireRow.Hidden = False Dim filename As String: filename = GetFileName(Range("A" & i)) ActiveWorkbook.Worksheets("Sheet1").Range("A1:B3").ExportAsFixedFormat Type:=xlTypePDF, _ filename:=filename, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False Rows(i).EntireRow.Hidden = True Next i Rows("1:3").EntireRow.Hidden = False End Sub – Andrew Svec Jul 12 '21 at 16:51

0 Answers0