More Macro Madness
 

i have an excel worksheet. one of the columns in this
worksheet lists the appropriate state abbreviation for
each record (ie., FL, NY, CA, TX, etc.). i would like to
sort my worksheet by state, and then break my worksheet up
into several different worksheets (if this is even
possible).........so that instead of having one worksheet
with a bunch of different states, i could have as many
worksheets as there are states listed.

i'd like to go from one worksheet with the states i listed
above........to something like:

worksheet 1: all the FL records
worksheet 2: all the NY records
worksheet 3: all the CA records
worksheet 4: all the TX records
and so on and so forth.....

any ideas out there? thanks in advance!

More Macro Madness
 

Assume the data you want to copy is on Sheet1, columns A-E with
states in C, and row1 is a header and you want a header on the new
sheets. Then the following will do it. Adjust for different assumptions.

Sub Macro1()
Dim iRow1 As Long
Dim iRow2 As Long
Dim strState As String

iRow1 = Sheets("Sheet1").Range("C65536").End(xlUp).Row
Sheets("Sheet1").Range("A1:E" & iRow1).Sort _
Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
iRow1 = 2
Do
If Sheets("Sheet1").Cells(iRow1, 3) < strState Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
strState = Sheets("Sheet1").Cells(iRow1, 3)
Worksheets(Worksheets.Count).Name = strState
iRow2 = 2
End If
Sheets("Sheet1").Range("A" & iRow1 & ":E" & iRow1).Copy _
Destination:=Worksheets(Worksheets.Count).Range("A " & iRow2 & ":E"
& iRow2)
iRow1 = iRow1 + 1
iRow2 = iRow2 + 1
Loop Until Sheets("Sheet1").Cells(iRow1, 3) = ""
End Sub

HTH,
Merjet

More Macro Madness
 

thanks, jet.......

this portion of your code turns RED in my VBE:

Sheets("Sheet1").Range("A" & iRow1 & ":E" & iRow1).Copy _
Destination:=Worksheets(Worksheets.Count).Range
("A" & iRow2 & ":E"
& iRow2)

any ideas? thanks.

josh
-----Original Message-----
Assume the data you want to copy is on Sheet1, columns A-
E with
states in C, and row1 is a header and you want a header
on the new
sheets. Then the following will do it. Adjust for
different assumptions.

Sub Macro1()
Dim iRow1 As Long
Dim iRow2 As Long
Dim strState As String

iRow1 = Sheets("Sheet1").Range("C65536").End(xlUp).Row
Sheets("Sheet1").Range("A1:E" & iRow1).Sort _
Key1:=Range("C2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
iRow1 = 2
Do
If Sheets("Sheet1").Cells(iRow1, 3) < strState Then
Worksheets.Add after:=Worksheets
(Worksheets.Count)
strState = Sheets("Sheet1").Cells(iRow1, 3)
Worksheets(Worksheets.Count).Name = strState
iRow2 = 2
End If
Sheets("Sheet1").Range("A" & iRow1 & ":E" &
iRow1).Copy _
Destination:=Worksheets(Worksheets.Count).Range
("A" & iRow2 & ":E"
& iRow2)
iRow1 = iRow1 + 1
iRow2 = iRow2 + 1
Loop Until Sheets("Sheet1").Cells(iRow1, 3) = ""
End Sub

HTH,
Merjet


.

More Macro Madness
 

actually the error was:

Runtime error "9"
subscript out of range.

where am i messing this up?
-----Original Message-----
Assume the data you want to copy is on Sheet1, columns A-
E with
states in C, and row1 is a header and you want a header
on the new
sheets. Then the following will do it. Adjust for
different assumptions.

Sub Macro1()
Dim iRow1 As Long
Dim iRow2 As Long
Dim strState As String

iRow1 = Sheets("Sheet1").Range("C65536").End(xlUp).Row
Sheets("Sheet1").Range("A1:E" & iRow1).Sort _
Key1:=Range("C2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
iRow1 = 2
Do
If Sheets("Sheet1").Cells(iRow1, 3) < strState Then
Worksheets.Add after:=Worksheets
(Worksheets.Count)
strState = Sheets("Sheet1").Cells(iRow1, 3)
Worksheets(Worksheets.Count).Name = strState
iRow2 = 2
End If
Sheets("Sheet1").Range("A" & iRow1 & ":E" &
iRow1).Copy _
Destination:=Worksheets(Worksheets.Count).Range
("A" & iRow2 & ":E"
& iRow2)
iRow1 = iRow1 + 1
iRow2 = iRow2 + 1
Loop Until Sheets("Sheet1").Cells(iRow1, 3) = ""
End Sub

HTH,
Merjet


.

More Macro Madness
 

this portion of your code turns RED in my VBE:

Sheets("Sheet1").Range("A" & iRow1 & ":E" & iRow1).Copy _
Destination:=Worksheets(Worksheets.Count).Range
("A" & iRow2 & ":E"
& iRow2)

Put the cursor at the end of the 2nd line and use the delete key
until the 2nd-4th lines are on one line (w/o spaces).

HTH,
Merjet

More Macro Madness
 

this will create the worksheets

Private Sub CommandButton1_Click()

x = 1 ' starting Row
y = 2
Do While Range("A" & x) < "" ' change "A" to your column where the
states are.

Sheets("Sheet2").Select
Sheets("Sheet1").Copy After:=Sheet1

Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = Sheet1.Cells(x, 1)
x = x + 1: y = y + 1
Loop

End Sub

Then you can just use a loop and an "if" statement to move the records to
the correct worksheet.




"Josh in Tampa" wrote in message
...
i have an excel worksheet. one of the columns in this
worksheet lists the appropriate state abbreviation for
each record (ie., FL, NY, CA, TX, etc.). i would like to
sort my worksheet by state, and then break my worksheet up
into several different worksheets (if this is even
possible).........so that instead of having one worksheet
with a bunch of different states, i could have as many
worksheets as there are states listed.

i'd like to go from one worksheet with the states i listed
above........to something like:

worksheet 1: all the FL records
worksheet 2: all the NY records
worksheet 3: all the CA records
worksheet 4: all the TX records
and so on and so forth.....

any ideas out there? thanks in advance!