Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to multiple worksheets via a variable
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 if you think it would be easier to assist me with this outside the forum (I may have more questions). MIKE |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to multiple worksheets via a variable
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?) *** 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) *** 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 *** 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) *** This is what you want: if (ActiveSheet.Range ("B8").value = 1) then ActiveBook.Worksheets("Worksheet4").Range("C4").va lue = ActiveBook.Worksheets("Worksheet1").Range("B9").va lue 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 *** Again, you placed this in the wrong place. Loop Again, thanks to all who can help. Feel free to email me at if you think it would be easier to assist me with this outside the forum (I may have more questions). MIKE |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to multiple worksheets via a variable
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" wrote in message ... 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 if you think it would be easier to assist me with this outside the forum (I may have more questions). MIKE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to a variable file name | Excel Discussion (Misc queries) | |||
Referring to multiple worksheets | Excel Worksheet Functions | |||
How can I use a variable to refer to multiple worksheets? | Excel Programming | |||
Referring to a Worksheet with a variable | Excel Programming | |||
Referring to a worksheet using variable | Excel Programming |