-
Does anyone know how to apply an array to the following code so that I can use this for 4 sheets instead of replicating the code 4 times?
Thanks in advance for your help!!!!!!!!!
Sub DoLoop()
'
' DoLoop Macro
'
Dim i As Integer
Dim sNCell As String
Dim sOCell As String
'Worksheets("Sheet1").Range("N2:R2").Copy 'Range to be copied
Set currentCell = Worksheets("Sheet1").Range("B2")
i = 1
Do While Not IsEmpty(currentCell)
'''' The following cells are being 'defined' and incremented by 1
'''' each time thru the loop to continue going down the spreadsheet
i = i + 1
sNCell = "N" & i '' N_ cell
sOCell = "O" & i '' O_ cell
sPCell = "P" & i '' P_ cell
sQCell = "Q" & i '' Q_ cell
sRCell = "R" & i '' R_ cell
'''' the next line is only for debugging purposes; it can be taken out
'''' go to the 'View' menu, and then 'Immediate Window' to see its
'''' results; it prints whatever you want in this window; very handy
Debug.Print currentCell
'''' each of the following lines copies the formula from the '?2' row
'''' and pastes into the 'defined' row from above
Worksheets("Sheet1").Range("N2").Copy _
Destination:=Worksheets("Sheet1").Range(sNCell)
Worksheets("Sheet1").Range("O2").Copy _
Destination:=Worksheets("Sheet1").Range(sOCell)
''' Next increment by 1 down to the next cell in the B column
''' to see if the loop should continue looping
Set nextCell = currentCell.Offset(1, 0)
Set currentCell = nextCell
Debug.Print currentCell
Loop
Application.CutCopyMode = False
Range("A2").Select
End Sub
-
Update on my question........
I have modified my code as follows:
Dim myWS as WorkSheet
For Each myWS in WorkSheets
' ....
'Code
' ....
Next
I replaced each Sheet1 with myWS
This works with one exception.... The code copies to every third line instead of each line to the end of file for each tab.
Any ideas??? Help is appreciated.
