JERID - Quick question for you (or anyone else)!
Posted by Dave on January 14, 2002 2:38 AM
Hi Jerid.
Thanks for the code you posted.
It was nearlly spot on but, I still have a couple of questions...
How do i change it so that all columns up to "CL" are transfered not just column A and B?
Also, how do i ensure that blank columns are transfered too (these are important to the file format)?
I've reposted the code in case you cant remember (or haven't seen it).
For more details see my earlier post at: 14654.html
Thanks for you help, code follows.
'Assuming that your data is in columns A & B with no empty cells from row 1 to the end of your data this will work for you.
Public Sub SplitSheet()
Dim CurNum As String
Dim sControlBook As String
Dim sNewBook As String
Dim sNameArray() As String
Dim lCounter As Long
Dim lX As Long
'Collect the name of the current workbook
sControlBook = ActiveWorkbook.Name
'Sort the data
Columns("A:B").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
application.Range("A1").Select
application.SheetsInNewWorkbook = 1
'Start our work
Do While ActiveCell.Value <> vbNullString
CurNum = ActiveCell.Value
Workbooks.Add
ActiveWorkbook.SaveAs (CurNum & "_" & Left(sControlBook, InStr(1, sControlBook, ".") - 1))
sNewBook = ActiveWorkbook.Name
application.Range("A1").Select
Workbooks(sControlBook).Activate
'Houskeeping
lCounter = 0
ReDim sNameArray(lCounter)
'Collect Names
Do While ActiveCell.Value = CurNum
lCounter = lCounter + 1
ReDim Preserve sNameArray(lCounter)
sNameArray(lCounter) = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
Loop
'Write data to new Workbook
Workbooks(sNewBook).Activate
For lX = 1 To UBound(sNameArray)
ActiveCell.Value = CurNum
ActiveCell.Offset(0, 1).Value = sNameArray(lX)
ActiveCell.Offset(1, 0).Select
Next lX
Workbooks(sNewBook).Close True
Workbooks(sControlBook).Activate
Loop
End Sub
Posted by Jerid on January 14, 2002 5:35 AM
This should do it for you. Replace the other procedure I sent with this one.
Public Sub SplitSheet()
Dim CurNum As String
Dim sControlBook As String
Dim sNewBook As String
'Collect the name of the current workbook
sControlBook = ActiveWorkbook.Name
'Sort the data
Columns("A:CL").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
application.Range("A1").Select
application.SheetsInNewWorkbook = 1
'Start our work
Do While ActiveCell.Value <> vbNullString
CurNum = ActiveCell.Value
Workbooks.Add
ActiveWorkbook.SaveAs (CurNum & "_" & Left(sControlBook, InStr(1, sControlBook, ".") - 1))
sNewBook = ActiveWorkbook.Name
application.Range("A1").Select
Workbooks(sControlBook).Activate
'Collect Names
Do While ActiveCell.Value = CurNum
ActiveCell.EntireRow.Copy
Workbooks(sNewBook).Activate
ActiveCell.EntireRow.PasteSpecial xlPasteAll
ActiveCell.Offset(1, 0).Select
Workbooks(sControlBook).Activate
ActiveCell.Offset(1, 0).Select
Loop
Workbooks(sNewBook).Close True
Workbooks(sControlBook).Activate
Loop
End Sub
Posted by Dave on January 14, 2002 6:05 AM
Thanks a lot. Here's what I came up with...
Hi Jerid,
Thats a much shorter code than what i came up with!!
The way i found to do it was to add lots more of the activecell offset idea... it ended up with large numbers of rows of code!
Just to give you a laugh, here it is! they do the same thing in two different waysbut, your's is a lot tidier!
Thanks for your help
Dave
Code follows (with appologies for its length!):
Public Sub SplitSheet2()
'Assuming that the data is in the first 90 columns with no empty rows before the end of the data.
Dim CurNum As String
Dim sControlBook As String
Dim sNewBook As String
Dim sNameArray1() As String
Dim sNameArray2() As String
Dim sNameArray3() As String
Dim sNameArray4() As String
Dim sNameArray5() As String
Dim sNameArray6() As String
Dim sNameArray7() As String
Dim sNameArray8() As String
Dim sNameArray9() As String
Dim sNameArray10() As String
Dim sNameArray11() As String
Dim sNameArray12() As String
Dim sNameArray13() As String
Dim sNameArray14() As String
Dim sNameArray15() As String
Dim sNameArray16() As String
Dim sNameArray17() As String
Dim sNameArray18() As String
Dim sNameArray19() As String
Dim sNameArray20() As String
Dim sNameArray21() As String
Dim sNameArray22() As String
Dim sNameArray23() As String
Dim sNameArray24() As String
Dim sNameArray25() As String
Dim sNameArray26() As String
Dim sNameArray27() As String
Dim sNameArray28() As String
Dim sNameArray29() As String
Dim sNameArray30() As String
Dim sNameArray31() As String
Dim sNameArray32() As String
Dim sNameArray33() As String
Dim sNameArray34() As String
Dim sNameArray35() As String
Dim sNameArray36() As String
Dim sNameArray37() As String
Dim sNameArray38() As String
Dim sNameArray39() As String
Dim sNameArray40() As String
Dim sNameArray41() As String
Dim sNameArray42() As String
Dim sNameArray43() As String
Dim sNameArray44() As String
Dim sNameArray45() As String
Dim sNameArray46() As String
Dim sNameArray47() As String
Dim sNameArray48() As String
Dim sNameArray49() As String
Dim sNameArray50() As String
Dim sNameArray51() As String
Dim sNameArray52() As String
Dim sNameArray53() As String
Dim sNameArray54() As String
Dim sNameArray55() As String
Dim sNameArray56() As String
Dim sNameArray57() As String
Dim sNameArray58() As String
Dim sNameArray59() As String
Dim sNameArray60() As String
Dim sNameArray61() As String
Dim sNameArray62() As String
Dim sNameArray63() As String
Dim sNameArray64() As String
Dim sNameArray65() As String
Dim sNameArray66() As String
Dim sNameArray67() As String
Dim sNameArray68() As String
Dim sNameArray69() As String
Dim sNameArray70() As String
Dim sNameArray71() As String
Dim sNameArray72() As String
Dim sNameArray73() As String
Dim sNameArray74() As String
Dim sNameArray75() As String
Dim sNameArray76() As String
Dim sNameArray77() As String
Dim sNameArray78() As String
Dim sNameArray79() As String
Dim sNameArray80() As String
Dim sNameArray81() As String
Dim sNameArray82() As String
Dim sNameArray83() As String
Dim sNameArray84() As String
Dim sNameArray85() As String
Dim sNameArray86() As String
Dim sNameArray87() As String
Dim sNameArray88() As String
Dim sNameArray89() As String
Dim sNameArray90() As String
Dim lCounter As Long
Dim lX As Long
'Collect the name of the current workbook
sControlBook = ActiveWorkbook.Name
'Sort the data
Columns("A:CL").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Application.Range("A1").Select
Application.SheetsInNewWorkbook = 1
'Start our work
Do While ActiveCell.value <> vbNullString
CurNum = ActiveCell.value
Workbooks.Add
ActiveWorkbook.SaveAs (CurNum & "_" & Left(sControlBook, InStr(1, sControlBook, ".") - 1))
sNewBook = ActiveWorkbook.Name
Application.Range("A1").Select
Workbooks(sControlBook).Activate
'Houskeeping
lCounter = 0
ReDim sNameArray1(lCounter)
ReDim sNameArray2(lCounter)
ReDim sNameArray3(lCounter)
ReDim sNameArray4(lCounter)
ReDim sNameArray5(lCounter)
ReDim sNameArray6(lCounter)
ReDim sNameArray7(lCounter)
ReDim sNameArray8(lCounter)
ReDim sNameArray9(lCounter)
ReDim sNameArray10(lCounter)
ReDim sNameArray11(lCounter)
ReDim sNameArray12(lCounter)
ReDim sNameArray13(lCounter)
ReDim sNameArray14(lCounter)
ReDim sNameArray15(lCounter)
ReDim sNameArray16(lCounter)
ReDim sNameArray17(lCounter)
ReDim sNameArray18(lCounter)
ReDim sNameArray19(lCounter)
ReDim sNameArray20(lCounter)
ReDim sNameArray21(lCounter)
ReDim sNameArray22(lCounter)
ReDim sNameArray23(lCounter)
ReDim sNameArray24(lCounter)
ReDim sNameArray25(lCounter)
ReDim sNameArray26(lCounter)
ReDim sNameArray27(lCounter)
ReDim sNameArray28(lCounter)
ReDim sNameArray29(lCounter)
ReDim sNameArray30(lCounter)
ReDim sNameArray31(lCounter)
ReDim sNameArray32(lCounter)
ReDim sNameArray33(lCounter)
ReDim sNameArray34(lCounter)
ReDim sNameArray35(lCounter)
ReDim sNameArray36(lCounter)
ReDim sNameArray37(lCounter)
ReDim sNameArray38(lCounter)
ReDim sNameArray39(lCounter)
ReDim sNameArray40(lCounter)
ReDim sNameArray41(lCounter)
ReDim sNameArray42(lCounter)
ReDim sNameArray43(lCounter)
ReDim sNameArray44(lCounter)
ReDim sNameArray45(lCounter)
ReDim sNameArray46(lCounter)
ReDim sNameArray47(lCounter)
ReDim sNameArray48(lCounter)
ReDim sNameArray49(lCounter)
ReDim sNameArray50(lCounter)
ReDim sNameArray51(lCounter)
ReDim sNameArray52(lCounter)
ReDim sNameArray53(lCounter)
ReDim sNameArray54(lCounter)
ReDim sNameArray55(lCounter)
ReDim sNameArray56(lCounter)
ReDim sNameArray57(lCounter)
ReDim sNameArray58(lCounter)
ReDim sNameArray59(lCounter)
ReDim sNameArray60(lCounter)
ReDim sNameArray61(lCounter)
ReDim sNameArray62(lCounter)
ReDim sNameArray63(lCounter)
ReDim sNameArray64(lCounter)
ReDim sNameArray65(lCounter)
ReDim sNameArray66(lCounter)
ReDim sNameArray67(lCounter)
ReDim sNameArray68(lCounter)
ReDim sNameArray69(lCounter)
ReDim sNameArray70(lCounter)
ReDim sNameArray71(lCounter)
ReDim sNameArray72(lCounter)
ReDim sNameArray73(lCounter)
ReDim sNameArray74(lCounter)
ReDim sNameArray75(lCounter)
ReDim sNameArray76(lCounter)
ReDim sNameArray77(lCounter)
ReDim sNameArray78(lCounter)
ReDim sNameArray79(lCounter)
ReDim sNameArray80(lCounter)
ReDim sNameArray81(lCounter)
ReDim sNameArray82(lCounter)
ReDim sNameArray83(lCounter)
ReDim sNameArray84(lCounter)
ReDim sNameArray85(lCounter)
ReDim sNameArray86(lCounter)
ReDim sNameArray87(lCounter)
ReDim sNameArray88(lCounter)
ReDim sNameArray89(lCounter)
ReDim sNameArray90(lCounter)
'Collect Names
Do While ActiveCell.value = CurNum
lCounter = lCounter + 1
ReDim Preserve sNameArray1(lCounter)
sNameArray1(lCounter) = ActiveCell.Offset(0, 1).value
ReDim Preserve sNameArray2(lCounter)
sNameArray2(lCounter) = ActiveCell.Offset(0, 2).value
ReDim Preserve sNameArray3(lCounter)
sNameArray3(lCounter) = ActiveCell.Offset(0, 3).value
ReDim Preserve sNameArray4(lCounter)
sNameArray4(lCounter) = ActiveCell.Offset(0, 4).value
ReDim Preserve sNameArray5(lCounter)
sNameArray5(lCounter) = ActiveCell.Offset(0, 5).value
ReDim Preserve sNameArray6(lCounter)
sNameArray6(lCounter) = ActiveCell.Offset(0, 6).value
ReDim Preserve sNameArray7(lCounter)
sNameArray7(lCounter) = ActiveCell.Offset(0, 7).value
ReDim Preserve sNameArray8(lCounter)
sNameArray8(lCounter) = ActiveCell.Offset(0, 8).value
ReDim Preserve sNameArray9(lCounter)
sNameArray9(lCounter) = ActiveCell.Offset(0, 9).value
ReDim Preserve sNameArray10(lCounter)
sNameArray10(lCounter) = ActiveCell.Offset(0, 10).value
ReDim Preserve sNameArray11(lCounter)
sNameArray11(lCounter) = ActiveCell.Offset(0, 11).value
ReDim Preserve sNameArray12(lCounter)
sNameArray12(lCounter) = ActiveCell.Offset(0, 12).value
ReDim Preserve sNameArray13(lCounter)
sNameArray13(lCounter) = ActiveCell.Offset(0, 13).value
ReDim Preserve sNameArray14(lCounter)
sNameArray14(lCounter) = ActiveCell.Offset(0, 14).value
ReDim Preserve sNameArray15(lCounter)
sNameArray15(lCounter) = ActiveCell.Offset(0, 15).value
ReDim Preserve sNameArray16(lCounter)
sNameArray16(lCounter) = ActiveCell.Offset(0, 16).value
ReDim Preserve sNameArray17(lCounter)
sNameArray17(lCounter) = ActiveCell.Offset(0, 17).value
ReDim Preserve sNameArray18(lCounter)
sNameArray18(lCounter) = ActiveCell.Offset(0, 18).value
ReDim Preserve sNameArray19(lCounter)
sNameArray19(lCounter) = ActiveCell.Offset(0, 19).value
ReDim Preserve sNameArray20(lCounter)
sNameArray20(lCounter) = ActiveCell.Offset(0, 20).value
ReDim Preserve sNameArray21(lCounter)
sNameArray21(lCounter) = ActiveCell.Offset(0, 21).value
ReDim Preserve sNameArray22(lCounter)
sNameArray22(lCounter) = ActiveCell.Offset(0, 22).value
ReDim Preserve sNameArray23(lCounter)
sNameArray23(lCounter) = ActiveCell.Offset(0, 23).value
ReDim Preserve sNameArray24(lCounter)
sNameArray24(lCounter) = ActiveCell.Offset(0, 24).value
ReDim Preserve sNameArray25(lCounter)
sNameArray25(lCounter) = ActiveCell.Offset(0, 25).value
ReDim Preserve sNameArray26(lCounter)
sNameArray26(lCounter) = ActiveCell.Offset(0, 26).value
ReDim Preserve sNameArray27(lCounter)
sNameArray27(lCounter) = ActiveCell.Offset(0, 27).value
ReDim Preserve sNameArray28(lCounter)
sNameArray28(lCounter) = ActiveCell.Offset(0, 28).value
ReDim Preserve sNameArray29(lCounter)
sNameArray29(lCounter) = ActiveCell.Offset(0, 29).value
ReDim Preserve sNameArray30(lCounter)
sNameArray30(lCounter) = ActiveCell.Offset(0, 30).value
ReDim Preserve sNameArray31(lCounter)
sNameArray31(lCounter) = ActiveCell.Offset(0, 31).value
ReDim Preserve sNameArray32(lCounter)
sNameArray32(lCounter) = ActiveCell.Offset(0, 32).value
ReDim Preserve sNameArray33(lCounter)
sNameArray33(lCounter) = ActiveCell.Offset(0, 33).value
ReDim Preserve sNameArray34(lCounter)
sNameArray34(lCounter) = ActiveCell.Offset(0, 34).value
ReDim Preserve sNameArray35(lCounter)
sNameArray35(lCounter) = ActiveCell.Offset(0, 35).value
ReDim Preserve sNameArray36(lCounter)
sNameArray36(lCounter) = ActiveCell.Offset(0, 36).value
ReDim Preserve sNameArray37(lCounter)
sNameArray37(lCounter) = ActiveCell.Offset(0, 37).value
ReDim Preserve sNameArray38(lCounter)
sNameArray38(lCounter) = ActiveCell.Offset(0, 38).value
ReDim Preserve sNameArray39(lCounter)
sNameArray39(lCounter) = ActiveCell.Offset(0, 39).value
ReDim Preserve sNameArray40(lCounter)
sNameArray40(lCounter) = ActiveCell.Offset(0, 40).value
ReDim Preserve sNameArray41(lCounter)
sNameArray41(lCounter) = ActiveCell.Offset(0, 41).value
ReDim Preserve sNameArray42(lCounter)
sNameArray42(lCounter) = ActiveCell.Offset(0, 42).value
ReDim Preserve sNameArray43(lCounter)
sNameArray43(lCounter) = ActiveCell.Offset(0, 43).value
ReDim Preserve sNameArray44(lCounter)
sNameArray44(lCounter) = ActiveCell.Offset(0, 44).value
ReDim Preserve sNameArray45(lCounter)
sNameArray45(lCounter) = ActiveCell.Offset(0, 45).value
ReDim Preserve sNameArray46(lCounter)
sNameArray46(lCounter) = ActiveCell.Offset(0, 46).value
ReDim Preserve sNameArray47(lCounter)
sNameArray47(lCounter) = ActiveCell.Offset(0, 47).value
ReDim Preserve sNameArray48(lCounter)
sNameArray48(lCounter) = ActiveCell.Offset(0, 48).value
ReDim Preserve sNameArray49(lCounter)
sNameArray49(lCounter) = ActiveCell.Offset(0, 49).value
ReDim Preserve sNameArray50(lCounter)
sNameArray50(lCounter) = ActiveCell.Offset(0, 50).value
ReDim Preserve sNameArray51(lCounter)
sNameArray51(lCounter) = ActiveCell.Offset(0, 51).value
ReDim Preserve sNameArray52(lCounter)
sNameArray52(lCounter) = ActiveCell.Offset(0, 52).value
ReDim Preserve sNameArray53(lCounter)
sNameArray53(lCounter) = ActiveCell.Offset(0, 53).value
ReDim Preserve sNameArray54(lCounter)
sNameArray54(lCounter) = ActiveCell.Offset(0, 54).value
ReDim Preserve sNameArray55(lCounter)
sNameArray55(lCounter) = ActiveCell.Offset(0, 55).value
ReDim Preserve sNameArray56(lCounter)
sNameArray56(lCounter) = ActiveCell.Offset(0, 56).value
ReDim Preserve sNameArray57(lCounter)
sNameArray57(lCounter) = ActiveCell.Offset(0, 57).value
ReDim Preserve sNameArray58(lCounter)
sNameArray58(lCounter) = ActiveCell.Offset(0, 58).value
ReDim Preserve sNameArray59(lCounter)
sNameArray59(lCounter) = ActiveCell.Offset(0, 59).value
ReDim Preserve sNameArray60(lCounter)
sNameArray60(lCounter) = ActiveCell.Offset(0, 60).value
ReDim Preserve sNameArray61(lCounter)
sNameArray61(lCounter) = ActiveCell.Offset(0, 61).value
ReDim Preserve sNameArray62(lCounter)
sNameArray62(lCounter) = ActiveCell.Offset(0, 62).value
ReDim Preserve sNameArray63(lCounter)
sNameArray63(lCounter) = ActiveCell.Offset(0, 63).value
ReDim Preserve sNameArray64(lCounter)
sNameArray64(lCounter) = ActiveCell.Offset(0, 64).value
ReDim Preserve sNameArray65(lCounter)
sNameArray65(lCounter) = ActiveCell.Offset(0, 65).value
ReDim Preserve sNameArray66(lCounter)
sNameArray66(lCounter) = ActiveCell.Offset(0, 66).value
ReDim Preserve sNameArray67(lCounter)
sNameArray67(lCounter) = ActiveCell.Offset(0, 67).value
ReDim Preserve sNameArray68(lCounter)
sNameArray68(lCounter) = ActiveCell.Offset(0, 68).value
ReDim Preserve sNameArray69(lCounter)
sNameArray69(lCounter) = ActiveCell.Offset(0, 69).value
ReDim Preserve sNameArray70(lCounter)
sNameArray70(lCounter) = ActiveCell.Offset(0, 70).value
ReDim Preserve sNameArray71(lCounter)
sNameArray71(lCounter) = ActiveCell.Offset(0, 71).value
ReDim Preserve sNameArray72(lCounter)
sNameArray72(lCounter) = ActiveCell.Offset(0, 72).value
ReDim Preserve sNameArray73(lCounter)
sNameArray73(lCounter) = ActiveCell.Offset(0, 73).value
ReDim Preserve sNameArray74(lCounter)
sNameArray74(lCounter) = ActiveCell.Offset(0, 74).value
ReDim Preserve sNameArray75(lCounter)
sNameArray75(lCounter) = ActiveCell.Offset(0, 75).value
ReDim Preserve sNameArray76(lCounter)
sNameArray76(lCounter) = ActiveCell.Offset(0, 76).value
ReDim Preserve sNameArray77(lCounter)
sNameArray77(lCounter) = ActiveCell.Offset(0, 77).value
ReDim Preserve sNameArray78(lCounter)
sNameArray78(lCounter) = ActiveCell.Offset(0, 78).value
ReDim Preserve sNameArray79(lCounter)
sNameArray79(lCounter) = ActiveCell.Offset(0, 79).value
ReDim Preserve sNameArray80(lCounter)
sNameArray80(lCounter) = ActiveCell.Offset(0, 80).value
ReDim Preserve sNameArray81(lCounter)
sNameArray81(lCounter) = ActiveCell.Offset(0, 81).value
ReDim Preserve sNameArray82(lCounter)
sNameArray82(lCounter) = ActiveCell.Offset(0, 82).value
ReDim Preserve sNameArray83(lCounter)
sNameArray83(lCounter) = ActiveCell.Offset(0, 83).value
ReDim Preserve sNameArray84(lCounter)
sNameArray84(lCounter) = ActiveCell.Offset(0, 84).value
ReDim Preserve sNameArray85(lCounter)
sNameArray85(lCounter) = ActiveCell.Offset(0, 85).value
ReDim Preserve sNameArray86(lCounter)
sNameArray86(lCounter) = ActiveCell.Offset(0, 86).value
ReDim Preserve sNameArray87(lCounter)
sNameArray87(lCounter) = ActiveCell.Offset(0, 87).value
ReDim Preserve sNameArray88(lCounter)
sNameArray88(lCounter) = ActiveCell.Offset(0, 88).value
ReDim Preserve sNameArray89(lCounter)
sNameArray89(lCounter) = ActiveCell.Offset(0, 89).value
ReDim Preserve sNameArray90(lCounter)
sNameArray90(lCounter) = ActiveCell.Offset(0, 90).value
ActiveCell.Offset(1, 0).Select
Loop
'Write data to new Workbook
Workbooks(sNewBook).Activate
For lX = 1 To UBound(sNameArray90)
ActiveCell.value = CurNum
ActiveCell.Offset(0, 1).value = sNameArray1(lX)
ActiveCell.Offset(0, 2).value = sNameArray2(lX)
ActiveCell.Offset(0, 3).value = sNameArray3(lX)
ActiveCell.Offset(0, 4).value = sNameArray4(lX)
ActiveCell.Offset(0, 5).value = sNameArray5(lX)
ActiveCell.Offset(0, 6).value = sNameArray6(lX)
ActiveCell.Offset(0, 7).value = sNameArray7(lX)
ActiveCell.Offset(0, 8).value = sNameArray8(lX)
ActiveCell.Offset(0, 9).value = sNameArray9(lX)
ActiveCell.Offset(0, 10).value = sNameArray10(lX)
ActiveCell.Offset(0, 11).value = sNameArray11(lX)
ActiveCell.Offset(0, 12).value = sNameArray12(lX)
ActiveCell.Offset(0, 13).value = sNameArray13(lX)
ActiveCell.Offset(0, 14).value = sNameArray14(lX)
ActiveCell.Offset(0, 15).value = sNameArray15(lX)
ActiveCell.Offset(0, 16).value = sNameArray16(lX)
ActiveCell.Offset(0, 17).value = sNameArray17(lX)
ActiveCell.Offset(0, 18).value = sNameArray18(lX)
ActiveCell.Offset(0, 19).value = sNameArray19(lX)
ActiveCell.Offset(0, 20).value = sNameArray20(lX)
ActiveCell.Offset(0, 21).value = sNameArray21(lX)
ActiveCell.Offset(0, 22).value = sNameArray22(lX)
ActiveCell.Offset(0, 23).value = sNameArray23(lX)
ActiveCell.Offset(0, 24).value = sNameArray24(lX)
ActiveCell.Offset(0, 25).value = sNameArray25(lX)
ActiveCell.Offset(0, 26).value = sNameArray26(lX)
ActiveCell.Offset(0, 27).value = sNameArray27(lX)
ActiveCell.Offset(0, 28).value = sNameArray28(lX)
ActiveCell.Offset(0, 29).value = sNameArray29(lX)
ActiveCell.Offset(0, 30).value = sNameArray30(lX)
ActiveCell.Offset(0, 31).value = sNameArray31(lX)
ActiveCell.Offset(0, 32).value = sNameArray32(lX)
ActiveCell.Offset(0, 33).value = sNameArray33(lX)
ActiveCell.Offset(0, 34).value = sNameArray34(lX)
ActiveCell.Offset(0, 35).value = sNameArray35(lX)
ActiveCell.Offset(0, 36).value = sNameArray36(lX)
ActiveCell.Offset(0, 37).value = sNameArray37(lX)
ActiveCell.Offset(0, 38).value = sNameArray38(lX)
ActiveCell.Offset(0, 39).value = sNameArray39(lX)
ActiveCell.Offset(0, 40).value = sNameArray40(lX)
ActiveCell.Offset(0, 41).value = sNameArray41(lX)
ActiveCell.Offset(0, 42).value = sNameArray42(lX)
ActiveCell.Offset(0, 43).value = sNameArray43(lX)
ActiveCell.Offset(0, 44).value = sNameArray44(lX)
ActiveCell.Offset(0, 45).value = sNameArray45(lX)
ActiveCell.Offset(0, 46).value = sNameArray46(lX)
ActiveCell.Offset(0, 47).value = sNameArray47(lX)
ActiveCell.Offset(0, 48).value = sNameArray48(lX)
ActiveCell.Offset(0, 49).value = sNameArray49(lX)
ActiveCell.Offset(0, 50).value = sNameArray50(lX)
ActiveCell.Offset(0, 51).value = sNameArray51(lX)
ActiveCell.Offset(0, 52).value = sNameArray52(lX)
ActiveCell.Offset(0, 53).value = sNameArray53(lX)
ActiveCell.Offset(0, 54).value = sNameArray54(lX)
ActiveCell.Offset(0, 55).value = sNameArray55(lX)
ActiveCell.Offset(0, 56).value = sNameArray56(lX)
ActiveCell.Offset(0, 57).value = sNameArray57(lX)
ActiveCell.Offset(0, 58).value = sNameArray58(lX)
ActiveCell.Offset(0, 59).value = sNameArray59(lX)
ActiveCell.Offset(0, 60).value = sNameArray60(lX)
ActiveCell.Offset(0, 61).value = sNameArray61(lX)
ActiveCell.Offset(0, 62).value = sNameArray62(lX)
ActiveCell.Offset(0, 63).value = sNameArray63(lX)
ActiveCell.Offset(0, 64).value = sNameArray64(lX)
ActiveCell.Offset(0, 65).value = sNameArray65(lX)
ActiveCell.Offset(0, 66).value = sNameArray66(lX)
ActiveCell.Offset(0, 67).value = sNameArray67(lX)
ActiveCell.Offset(0, 68).value = sNameArray68(lX)
ActiveCell.Offset(0, 69).value = sNameArray69(lX)
ActiveCell.Offset(0, 70).value = sNameArray70(lX)
ActiveCell.Offset(0, 71).value = sNameArray71(lX)
ActiveCell.Offset(0, 72).value = sNameArray72(lX)
ActiveCell.Offset(0, 73).value = sNameArray73(lX)
ActiveCell.Offset(0, 74).value = sNameArray74(lX)
ActiveCell.Offset(0, 75).value = sNameArray75(lX)
ActiveCell.Offset(0, 76).value = sNameArray76(lX)
ActiveCell.Offset(0, 77).value = sNameArray77(lX)
ActiveCell.Offset(0, 78).value = sNameArray78(lX)
ActiveCell.Offset(0, 79).value = sNameArray79(lX)
ActiveCell.Offset(0, 80).value = sNameArray80(lX)
ActiveCell.Offset(0, 81).value = sNameArray81(lX)
ActiveCell.Offset(0, 82).value = sNameArray82(lX)
ActiveCell.Offset(0, 83).value = sNameArray83(lX)
ActiveCell.Offset(0, 84).value = sNameArray84(lX)
ActiveCell.Offset(0, 85).value = sNameArray85(lX)
ActiveCell.Offset(0, 86).value = sNameArray86(lX)
ActiveCell.Offset(0, 87).value = sNameArray87(lX)
ActiveCell.Offset(0, 88).value = sNameArray88(lX)
ActiveCell.Offset(0, 89).value = sNameArray89(lX)
ActiveCell.Offset(0, 90).value = sNameArray90(lX)
ActiveCell.Offset(1, 0).Select
Next lX
Workbooks(sNewBook).Close True
Workbooks(sControlBook).Activate
Loop
End Sub
Posted by Jerid on January 14, 2002 6:32 AM
Your Welcome
Hey, as long as it works, who cares what it looks like!
Another possible solution would be to use a User Defined Type and a Collection Object, you can find examples of this in help.
Good luck.
Jerid
!&media=https%3A%2F%2Fwww.mrexcel.com%2Fimg%2Fmrexcel-publishing.png){kind=link}