I need to refer to the same cells in multiple worksheets. How can I do this,
i.e. WorksheetX, where X is used in a loop increasing until all the sheets
are processed?

You have the Worksheets collection
e.g Worksheets(2)

NickHK

"dc_area_mcse" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:4BEDC0F7-8E2D-484E-919F-(E-Mail Removed)...
>I need to refer to the same cells in multiple worksheets. How can I do
>this,
> i.e. WorksheetX, where X is used in a loop increasing until all the sheets
> are processed?

If you really mean you have worksheets names
worksheet1
worksheet2
worksheet3
etc, then

Dim bcontinue as boolean, x as Long
Dim sh as Worksheet, rng as Range
bContinue = True
x = 1
do while true
On Error Resume Next
set sh = nothing
set sh = worksheets("Worksheet" & x)
if not sh is nothing then
set rng = sh.Range("A1:B9")
msgbox rng.Address(0,0,xlA1,True)
Else
bcontinue = False
end if
Loop

--
Regards,
Tom Ogilvy

"dc_area_mcse" wrote:

> I need to refer to the same cells in multiple worksheets. How can I do this,
> i.e. WorksheetX, where X is used in a loop increasing until all the sheets
> are processed?

Tom and Nick,

First of all thanks for your posts trying to help me. I'm new to this
and still a bit confused; perhaps I didn't explain the situation correctly.
I have a workbook that imports a number of text files from a website, each
file onto a different worksheet, starting at worksheet 4. Then I need it to
do a "text to columns" on cells A9-A11. Then I need the results of B9-11
stored in Cx-Ex, where x is the worksheet number. It should look *SOMETHING*
like this (please help me with the commands and my errors, my comments using
the ' (single quote, I believe that is the "ignore" sign in VB, right?):





x=4
Do until x=41 '(if the last worksheet is 40, correct?)

Sub Breakup() '(this is the code to do it on the current worksheet, again
I need to do this on multiple worksheets)

Range("A8").Select
Selection.TextToColumns Destination:=Range("A8"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("A9").Select
Selection.TextToColumns Destination:=Range("A9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("A10").Select
Selection.TextToColumns Destination:=Range("A10"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("A11").Select
Selection.TextToColumns Destination:=Range("A11"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
End Sub

If $B8 = 1 (if the value in B8 on that worksheet is 1)
Then '(this is I believe the code to get the values from Cells B9-11 on
worksheet4 and place them in cells C4-E4 on worksheet1)
Sheets("Sheet1").Select
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Sheet4!R[5]C[-1]"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=Sheet4!R[6]C[-2]"
Range("D5").Select

Else

x=x+1
Loop




Again, thanks to all who can help me feel free to email me at
(E-Mail Removed) if you think it would be easier to assist me with
this outside the forum (I may have more questions).

MIKE


"Tom Ogilvy" wrote:

> If you really mean you have worksheets names
> worksheet1
> worksheet2
> worksheet3
> etc, then
>
> Dim bcontinue as boolean, x as Long
> Dim sh as Worksheet, rng as Range
> bContinue = True
> x = 1
> do while true
> On Error Resume Next
> set sh = nothing
> set sh = worksheets("Worksheet" & x)
> if not sh is nothing then
> set rng = sh.Range("A1:B9")
> msgbox rng.Address(0,0,xlA1,True)
> Else
> bcontinue = False
> end if
> Loop
>
> --
> Regards,
> Tom Ogilvy
>
> "dc_area_mcse" wrote:
>
> > I need to refer to the same cells in multiple worksheets. How can I do this,
> > i.e. WorksheetX, where X is used in a loop increasing until all the sheets
> > are processed?