PDA

View Full Version : [SOLVED] With Statement with multiple arguments



anish201
07-29-2005, 08:41 AM
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
07-29-2005, 08:52 AM
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
07-29-2005, 10:57 PM
hehe...he said GoSub

excelliot
07-30-2005, 12:17 AM
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
07-30-2005, 08:13 AM
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....:devil:

chandansify
07-31-2005, 09:25 PM
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....:devil:



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


Chandan.

Steiner
08-01-2005, 12:35 AM
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