0

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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Oliver Ashmole
  • 1

2 Answers2

1

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

1

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).

Vityata
  • 42,633
  • 8
  • 55
  • 100