Vba to loop copy and past one cell at a time until blank

[sjramos]

Hi all,
I am confident you can help a novice like me. I want to copy one cell at a time from sheet2 (cell x2 until blank) and paste in sheet1(cell j3). I want to loop it with my code for printing pdf. So, when the cell is copied and pasted as values it will do the calculations and print pdf. Then copy n paste the next cell, Until it finds a blank cell and stops.

Thank you,

 

[Takae]

Which column have values in sheet2?
Assuming ColumnA in sheet2 has values, please try this code.

Sub pdf()
Dim cnt As Long, LastR As Long
Dim ws1 As Worksheet, ws2 As Worksheet, w
Const path = "C:\Users\User\Desktop\"
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

cnt = 1
With ws2
    For Each w In .Range(.Range("A2"), .Range("A2").End(xlDown))
        ws1.Range("J3").Value = w.Value
        ws1.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=path & cnt & "_" & Format(Date, "ddmm") & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        cnt = cnt + 1
    Next
End Sub

 

[sjramos]

Thank you , made a few tweaks and worked perfectly