Copy, Paste, Print to PDF and Loop (With Code)!

[Crypsis]

Hi All,

Thanks for taking the time to look at my post!

I am attempting to copy and paste data one by one from the range A4:A150 to cell M5. After each copy and paste, i would like the macro to save to PDF and then loop to the next cell from the range.

At the moment my code works for the first cell only (A4) and is not looping for the rest of the data.

Here is my code:

Sub PrintAll()[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    ThisWorkbook.Sheets("Structured Note Raw Data").Range("A4").Copy[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    ThisWorkbook.Sheets("Template").Range("M5").Select[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    ThisWorkbook.Sheets("Template").Paste[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim wsA As Worksheet[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim wbA As Workbook[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim strName As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim strPath As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim strFile As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim strPathFile As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim myFile As Variant[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]On Error GoTo errHandler[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set wbA = ActiveWorkbook[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set wsA = ActiveSheet[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'get active workbook folder, if saved[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]strPath = wbA.Path[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If strPath = "" Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  strPath = Application.DefaultFilePath[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]strPath = strPath & "\"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]strName = wsA.Range("M5").Value _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'create default name for savng file[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]strFile = strName & ".pdf"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]strPathFile = strPath & strFile[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'export to PDF in current folder[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    wsA.ExportAsFixedFormat _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        Type:=xlTypePDF, _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        Filename:=strPathFile, _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        Quality:=xlQualityStandard, _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        IncludeDocProperties:=True, _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        IgnorePrintAreas:=False, _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        OpenAfterPublish:=False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    'confirmation message with file info[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    MsgBox "PDF file has been created: " _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]      & vbCrLf _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]      & strPathFile[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]exitHandler:[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Exit Sub[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]errHandler:[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    MsgBox "Could not create PDF file"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Resume exitHandler[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim cell As Range[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'Loop through each cell in a cell range[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  For Each cell In ThisWorkbook.Sheets("Structured Note Raw Data").Range("A4:A150")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Debug.Print cell.Value[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  Next cell[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]

If you have any better ideas or ways or writing it then please let me know!

Thanks,
Crypsis

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel-questions/1056133-looping-vba-copy-paste-print-pdf-code.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).