Post data from table in a loop into a set cell in a separate sheet

[Auslove]

Hi All,

I am a novice at VBA and creating macros. Please excuse my ignorance.

I am looking for some help to create a MACRO to copy paste data from a table in one sheet into specific cells in a separate sheet. It will need to be in a loop. The idea is to create automatic PDFs (I've found help for this) from a data table. Each row in the table has unique data, which will be needed to be pasted into a separate sheet onto a dynamic / fixed cell. Copy pasting the first row into the specific cell, then clearing the cell after creating a PDF, then moving to the new row, then repeating the process until the end of the table.

Thank you,
AusLove

 

[John_w]

Try this macro. It assumes the data is in columns A:C in a sheet named "Data" and the payslip sheet is named "Payslip". Copies cells A:C to B3, B6, B9 on "Payslip". The .pdf files are named after the A and B cells and saved in the same folder as the workbook.

Public Sub Create_PDFs()

    Dim saveInFolder As String
    Dim payslipSheet As Worksheet
    Dim lastRow As Long, r As Long
   
    saveInFolder = ThisWorkbook.Path & "\"
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
   
    Set payslipSheet = Worksheets("Payslip")
   
    With Worksheets("Data")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For r = 2 To lastRow
            .Cells(r, "A").Copy payslipSheet.Range("B3")
            .Cells(r, "B").Copy payslipSheet.Range("B6")
            .Cells(r, "C").Copy payslipSheet.Range("B9")
            payslipSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & .Cells(r, "A").Value & " " & .Cells(r, "B").Value & ".pdf", Quality:=xlQualityStandard
        Next
    End With
   
    MsgBox "Done"
   
End Sub

 

[Auslove]

Thanks a lot John W. It works.