With Statement with multiple arguments

[anish201]

I am trying to perform a particular action in 3 worksheets. I dint want to write the code thrice. does anyone know what the right syntax is for the following

I am trying to do this

With Worksheets(xxx) & worksheets(yyy) & worksheets (zzz)
Perform some action


But it doesnt work, does anyone know how I can do this?

[lucas]

Try something like this. the 1-3 in the example are sheets to be copied to a 4th sheet.

For i = 1 To 3 
    Sheets(i).Select
    GoSub DoCopy
    GoSub DoPaste
    n = n + r
Next i

[xCav8r]

hehe...he said GoSub

[excelliot]

Try something like this. the 1-3 in the example are sheets to be copied to a 4th sheet.

For i = 1 To 3 
Sheets(i).Select
GoSub DoCopy
GoSub DoPaste
n = n + r
Next i

Did u said n= n+r or
it shuld be i=i+1

[lucas]

I should have posted more of the code to avoid confusion.

Sub ConsolLoop()
    Sheets(4).Select
    Cells.ClearContents
    r = 0
    n = 0
For i = 1 To 3
    Sheets(i).Select
    GoSub DoCopy
    GoSub DoPaste
    n = n + r
    Next i
Exit Sub
DoCopy:
    Cells(1, 1).CurrentRegion.Select
    Selection.Copy
    r = Selection.Rows.Count
Return
DoPaste:
    Sheets(4).Select
    Cells(1, 1).Offset(n, 0).Select
    ActiveSheet.Paste
    Return
End Sub

combines sheets 1-3 to sheet 4
the next i is just telling it to go to the next sheet.
I did say gosub....

[chandansify]

I should have posted more of the code to avoid confusion.

Sub ConsolLoop()
    Sheets(4).Select
    Cells.ClearContents
    r = 0
    n = 0
For i = 1 To 3
    Sheets(i).Select
    GoSub DoCopy
    GoSub DoPaste
    n = n + r
    Next i
Exit Sub
DoCopy:
    Cells(1, 1).CurrentRegion.Select
    Selection.Copy
    r = Selection.Rows.Count
    Return
DoPaste:
    Sheets(4).Select
    Cells(1, 1).Offset(n, 0).Select
    ActiveSheet.Paste
    Return
End Sub

combines sheets 1-3 to sheet 4
the next i is just telling it to go to the next sheet.
I did say gosub....

thanks for showing GoSub----Return statement..


Chandan.

[Steiner]

Hi anish201,
and you could just combine your with statement with such a loop, the result could look like that:

Option Explicit 
Sub test()
   Dim i%
For i = 1 To 3
   With Worksheets(i)
       Debug.Print .Name
    End With
   Next i
End Sub

But note that the code will work with one worksheet after another not all 3 of them at once.

Daniel