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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.