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