Referring to multiple worksheets via a variable

D

dc_area_mcse

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]" 'I need something similar
to Sheet(x)!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. Feel free to email me at
(e-mail address removed) if you think it would be easier to assist me with
this outside the forum (I may have more questions).

MIKE
 
D

dolivastro

There is a lot that's confused here. But I have some comments in-line,
starting with ***. Actually, I think the whole thing should be
reconsidered.

Hope it helps,
Dom


x=4
Do until x=41 '(if the last worksheet is 40, correct?)
Click to expand...
*** yes, that's correct. But you are placing "x=x+1" in the wrong
place.
Sub Breakup() '(this is the code to do it on the current worksheet, again
I need to do this on multiple worksheets)
Click to expand...
*** I don't like the SUB statement, and I'm not really sure it is still
used! Just write a true subroutine called "Breakup", and call it with
the line "Call Breakup ()". That keeps your code cleaner, too.
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
Click to expand...
*** Be carefull here. It looks like you are taking cell A8 and parsing
it to cells on the right. This will overwrite the information in cells
A9, A10, etc.

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)
Click to expand...
*** This is what you want:
if (ActiveSheet.Range ("B8").value = 1) then
ActiveBook.Worksheets("Worksheet4").Range("C4").value =
ActiveBook.Worksheets("Worksheet1").Range("B9").value
etc.
endif


Sheets("Sheet1").Select
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Sheet4!R[5]C[-1]" 'I need something similar
to Sheet(x)!R[5]C[-1]" ??
Range("D4").Select
ActiveCell.FormulaR1C1 = "=Sheet4!R[6]C[-2]"
Range("D5").Select

Else

x=x+1
Click to expand...
*** Again, you placed this in the wrong place.
 
B

Bernie Deitrick

Mike,

Try the macro below.

HTH,
Bernie
MS Excel MVP



Sub BreakupAll()
Dim i As Integer
Dim mySht As Worksheet
Dim myIndex As Integer

For Each mySht In Worksheets
If mySht.Name Like "Sheet*" Then
If Not IsError(CInt(Mid(mySht.Name, 6, 3))) Then
myIndex = CInt(Mid(mySht.Name, 6, 3))
If myIndex > 3 Then

mySht.Range("A8:A11").TextToColumns _
Destination:=mySht.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

If mySht.Range("B8").Value = 1 Then
For i = 3 To 5
'Use this if you want links
Sheets("Sheet1").Cells(myIndex, i).Formula = _
"=Sheet" & myIndex & "!B" & i + 6

'Use this if you want values and not links
' Sheets("Sheet1").Cells(myIndex, i).Value = _
mySht.Range("B" & i + 6).Value

Next i
End If
End If
End If
End If
Next mySht
End Sub



dc_area_mcse said:
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]" 'I need something similar
to Sheet(x)!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. Feel free to email me at
(e-mail address removed) if you think it would be easier to assist me with
this outside the forum (I may have more questions).

MIKE
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro error when formatting columns 6
Text To Columns On A Different Sheet 12
Date Format 4
Copy part of text file 4
Very EASY & SIMPLE VBA~!!! HELP msgbox 1
Open Text Method Fails 7
defining TextToColumns with variable 2
Copy from one worksheet to another 1

Top