Looping VBA Copy/Paste and Print to PDF (CODE)

[Crypsis]

Hi All,

Thanks in advance for your help.

I am trying to copy cells from a data set in rows A4:A150 into cell M5 on a different sheet. After each copy, i want the code to print to PDF and save this as is. When this is complete, i am trying to get the code to loop down to the next cell (so starting from A4 to A5 and so on).

This is the code i have, currently it will copy and paste the cell from A4 but it will not loop thereafter. Any suggestions?

Also, if you have any better ideas of writing this code then please let me know!

Sub PrintAll()

    ThisWorkbook.Sheets("Structured Note Raw Data").Range("A4").Copy
    ThisWorkbook.Sheets("Template").Range("M5").Select
    ThisWorkbook.Sheets("Template").Paste


Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"


strName = wsA.Range("M5").Value _


'create default name for savng file
strFile = strName & ".pdf"
strPathFile = strPath & strFile


'export to PDF in current folder
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & strPathFile


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler


Dim cell As Range


'Loop through each cell in a cell range
  For Each cell In ThisWorkbook.Sheets("Structured Note Raw Data").Range("A4:A150")
    Debug.Print cell.Value
  Next cell
  


End Sub

Thanks very much.

 

[Fluff]

How about

Sub PrintAll()



Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Dim cell As Range

On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

For Each cell In ThisWorkbook.Sheets("Structured Note Raw Data").Range("A4:A150")
    
    
   cell.Copy ThisWorkbook.Sheets("Template").Range("M5")
   strName = wsA.Range("M5").Value _


'create default name for savng file
strFile = strName & ".pdf"
strPathFile = strPath & strFile


'export to PDF in current folder
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        fileName:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & strPathFile
  Next cell


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

 

[Crypsis]

Fluff that is excellent!

Works like a dream, thank you very very much.

I'll look into it in a bit more detail so that I can learn about the changes you made.

Again, thanks so much and have a great weekend!

Best,
Crypsis

 

[Fluff]

Glad to help & thanks for the feedback