CODEWITHSUNDEEP
vbaexcelloops
Oliver Ashmole
Oliver Ashmole

Reputation: 1

Looping a macro for copy & pasting then printing

I need to create a loop for this macro:

Sub Site_No()
'   Site_No Macro
'   Keyboard Shortcut: Ctrl+Shift+J

    Range("D2").Select
    Selection.Copy
    Sheets("Spray Sheet").Select
    Range("F5:J6").Select
    ActiveSheet.Paste
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Ideally I'd like to loop it through from D2 - D79.

If someone could show me where to place the loop i'd be very grateful

Ollie

Upvotes: 0

Views: 260

Answers (2)

Vityata
Vityata

Reputation: 43595

Something like this should be enough:

Public Sub TestMe()    
    Dim lngRow      As Long        
    For lngRow = 2 To 79
        ActiveSheet.Cells(lngRow, "D") = Worksheets("Spray Sheet").Cells(lngRow, "F")
    Next lngRow    
End Sub

Using select and activate is something that you should try to avoid as much as possible - How to avoid using Select in Excel VBA (said by the guy who used ActiveSheet in his code).

Upvotes: 1

user8753746
user8753746

Reputation:

I will not provide you the code, but will give you some hints to achieve your goal.

Sub Site_No()
'
' Start your loop here from D1 to D79. I suggest to use Cells(Row, Column) notation.
   Range("D2").Select 'There is no need to select the cell
   Selection.Copy 'Instead of Copy, you can use .Value
   Sheets("Spray Sheet").Select 'There is no need to select the Sheet
   Range("F5:J6").Select 'There is no need to select the cell
   ActiveSheet.Paste 'You don't need to paste the value.
 'End loop here if you need to print only once.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
End Sub

You can see this question for an example to "copy/paste" cell content using .Value.

Copy/Paste Loop through worksheets to consolidate

Upvotes: 1

Related Questions