Print Macro help

[Ike Andoit]

Hi there,
I am trying to create a macro (my 1st ever) that will take a pack number listed in column B, starting at row 3, on a worksheet ("Packs 01-07-19 to Current"), copy it, paste that number in to cell C2 on another worksheet ("Entry form") then print and repeat the process using the information going down column B in the Packs worksheet until there is no information left in that column.

I tried recording what i was doing and it created this:

Sub Macro2()
'
' Macro2 Macro
'

'
Range("B3").Select
Selection.Copy
Sheets("Entry form").Select
Range("C2").Select
ActiveSheet.Paste
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub


Could someone please help me with this?

many thanks,

Nathan

 

[Joe4]

Welcome to the Board!

See if this does what you want:

Sub Macro2()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r As Long
    
'   Designate the two worksheets we are working with
    Set ws1 = Sheets("Packs 01-07-19 to Current")
    Set ws2 = Sheets("Entry Form")
    
'   Find last entry in column B on first sheet
    lr = ws1.Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all entries in column B
    For r = 3 To lr
'       Copy value from column B on ws1 to C2 in ws2
        ws1.Range("B" & r).Copy ws2.Range("C2")
'       Print out sheet
        ws2.Activate
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
'   Go to next row
    Next r

End Sub

 

[Ike Andoit]

Welcome to the Board!

See if this does what you want:

Sub Macro2()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r As Long
   
'   Designate the two worksheets we are working with
    Set ws1 = Sheets("Packs 01-07-19 to Current")
    Set ws2 = Sheets("Entry Form")
   
'   Find last entry in column B on first sheet
    lr = ws1.Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through all entries in column B
    For r = 3 To lr
'       Copy value from column B on ws1 to C2 in ws2
        ws1.Range("B" & r).Copy ws2.Range("C2")
'       Print out sheet
        ws2.Activate
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
'   Go to next row
    Next r

End Sub

Thanks heaps I will try this out :0)