VBA - failed to use loop

[striving12345]

I have the following code from a recorded macro and need to copy>paste special>print for approximately 100 rows down until a blank cell is detected.
- Have tried to edit to loop but failed.

Am very new to VBA. Would appreciate the help.

Sub Macro3()
'
' Macro3 Macro
'

'
Range("C2").Select
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("SO").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("Extract").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("SO").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("Extract").Select
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("SO").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub

 

[striving12345]

This is what I tried for just the copy and paste special portion but there was an error in "Sheets("Extract").Range("K2").Value = Sheets("Extract").Range(Cells(r, "A")).Value".

Dim lr As Long
Dim r As Long

'Find last row in column A with data
lr = Sheets("Extract").Cells(Rows.Count, "A").End(xlUp).Row

'Loop through all rows and paste values from A into K2
For r = 2 To lr
Sheets("Extract").Range("K2").Value = Sheets("Extract").Range(Cells(r, "A")).Value
Next r

 

[striving12345]

Managed to figure out the error and also included a print. It works!
- Supported with this thread: Loop to copy/paste next row using VBA

Thank you!

Sub Macro2()

    Dim lr As Long
    Dim r As Long
   
    'Find last row in column A with data
    lr = Sheets("Extract").Cells(Rows.Count, "A").End(xlUp).Row
    'Loop through all rows and paste values from A into K2
    For r = 2 To lr
        Sheets("Extract").Range("K2").Value = Sheets("Extract").Cells(r, "A").Value
    'Print
    Sheets("SO").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Next r

End Sub

 

[Peter_SSs]

Glad you got it resolved.

For the future: When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. (Compare code in posts 1 & 2 v. post 3 where I have added the tags for you this time. )
My signature block below has more details.