Print Macro help

Ike Andoit

New Member
Joined
Jun 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,515
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

See if this does what you want:
VBA Code:
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

New Member
Joined
Jun 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

See if this does what you want:
VBA Code:
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)
 

Forum statistics

Threads
1,176,265
Messages
5,902,239
Members
434,953
Latest member
matthiasarnbert

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top