Tech Support Guy banner
Status
Not open for further replies.
1 - 20 of 20 Posts

·
Registered
Joined
·
7,837 Posts
Discussion Starter · #1 ·
I have some code from long ago that kept my original data but also wrote the related data to new worksheets based on a change in column A (it said Region 1, Region 2, etc. there).

Now I want to do much the same thing based on a sample with the listings of states as the breaking point - that is, a different worksheet for each of the 36 states that appear in this rather large sample.

The code I have from before is:
Code:
Sub Split_Page_at_Change()
Application.ScreenUpdating = False
For i = 1 To 29
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Region " & i
Sheets("Sheet1").Range("A1:L1").Copy Range("A1")
Next i

Sheets("Sheet1").Select
x = Rows.Count
For Each Cell In Range("A2", Range("A2").End(xlDown))
Cell.Resize(1, 12).Copy Sheets(Cell.Value).Range("A" & x).End(xlUp).Offset(1)
Next Cell

Application.ScreenUpdating = True
End Sub
You can see it had 29 regions and it named each page Region 1, Region 2, etc but I would rather have the new worksheets draw their names from the abbreviations for the states, which are in column E (of course, I could move this, but it is a better layout with it there, following the cities!)

Thanks gang, and have wonderful holidays!!!! :D :up:
 

·
Registered
Joined
·
8,546 Posts
"the abbreviations for the states, which are in column E" is your idea of adequate info? :D

E1 = "State" -- E2 = "AL" -- E3 = "AZ" (the two that jumped in my head).

Sub test()
Homesheet = ActiveSheet.Name
For i = 1 To 2
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheets(Homesheet).Cells(i + 1, 5)
Next i
End Sub

adds an "AL" sheet and an "AZ" sheet. The "Cells" syntax is Cells(r,c) -- thus the first pass is Cells(r2,c5) (E2), the second is Cells(r3,c5) (E3).

See?

Season's greetings Loche. :)
 

·
Registered
Joined
·
7,837 Posts
Discussion Starter · #4 ·
So, like the following - which I use for putting in page breaks where there is a change in a column - I want to read down column E and make a new page when that changes and only include the data where the column E is all alike.

Code:
Sub Page_Break_at_Change()
Do Until ActiveCell = ""
If ActiveCell <> ActiveCell.Offset(1, 0) Then
ActiveCell.Offset(1, 0).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
 

·
Registered
Joined
·
8,546 Posts
So ... you should set up a unique list in a free area first, and then loop through that?

Last_E = Range("E" & Rows.Count).End(xlUp).Row
Range("E1:E" & Last_E).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("M1"), Unique:=True
 

·
Registered
Joined
·
7,837 Posts
Discussion Starter · #6 ·
So do essentially the same as a pivot table listing - only making a range in a column off to one side?
But then what? Not following...(I was out watching the eclipse late last night....)
 

·
Registered
Joined
·
8,546 Posts
"essentially the same as a pivot table listing", yes.

You can do it virtually if that's more your kind of "methodology", but at least ditch the ActiveCell.Offset(1, 0).Select horror.

Sub uniques()
Last_E = Range("E" & Rows.Count).End(xlUp).Row
For Each Cell In Range("E2:E" & Last_E)
If Cell <> Cell.Offset(-1) Then
MsgBox Cell 'substitute whatever else you need it to do here
End If
Next Cell
End Sub
 

·
Registered
Joined
·
8,546 Posts
A little more:

Sub uniques()
Last_E = Range("E" & Rows.Count).End(xlUp).Row
For Each Cell In Range("E2:E" & Last_E)
If Cell <> Cell.Offset(-1) Then
NewSheet = Cell.Value
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = NewSheet

End If
Next Cell
End Sub
 

·
Registered
Joined
·
7,837 Posts
Discussion Starter · #9 ·
LOL! The "ActiveCell.Offset(1, 0).Select horror" - hey, someone else wrote that for me and it works, whether it is good coding or not.
As for post # 8, that gives me all the new sheets I want but doesn't copy over the data related to them value in column E (and I want to copy what is in row #1, which consists of ID, name, city, ST, ZIP, etc., as a header to all the new sheets.)
Can you add that - maybe by matching the data in E to the sheet name???
 

·
Trusted Advisor
Joined
·
6,911 Posts
Hi, I'd like to take a look, see if you have a sample for me to work with.
I'm off on a 12 day holiday on the 24th so won't be able to do too much.
I'll be back on-line after the3d of January, but if you post a sample today I might get the results working. At least I think I understand what you need.
If I don't see anyhting, Marry X'Mas and a Happy 2011.

:) :) :)
 

·
Registered
Joined
·
8,546 Posts
"I want to copy what is in row #1" I understand.

"the data related to them value in column E" I don't understand -- unless you mean you want the (e.g.) AL rows to be copied to the new AL sheet.

Sub uniques()
Last_E = Range("E" & Rows.Count).End(xlUp).Row
For Each Cell In Sheets("Sheet1").Range("E2:E" & Last_E)
If Cell <> Cell.Offset(-1) Then
NewSheet = Cell.Value
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = NewSheet
Sheets("Sheet1").Range("A1:J1").Copy Range("A1")
End If
Next Cell
End Sub

EDIT: is this it? :D

Sub uniques()
Last_E = Range("E" & Rows.Count).End(xlUp).Row
For Each Cell In Sheets("Sheet1").Range("E2:E" & Last_E)
If Cell <> Cell.Offset(-1) Then
ST_Rows = WorksheetFunction.CountIf(Sheets("Sheet1").Columns(5), Cell.Value)
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = Cell.Value
Sheets("Sheet1").Range("A1:J1").Copy Range("A1")
Sheets("Sheet1").Cells(Cell.Row, 1).Resize(ST_Rows, 10).Copy Range("A2")
End If
Next Cell
End Sub
 

·
Trusted Advisor
Joined
·
6,911 Posts
got the sample:)
 

·
Registered
Joined
·
7,837 Posts
Discussion Starter · #16 ·
bomb, I took your code from #8 and ran off a mess of new worksheets, then imported the data into Access and broke it apart by state there using 36 different queries, and then I just pasted that data into the separate worksheets.
A bit more work than I had hoped to do (feeling lazy over the holiday season!) but really I have this done.
Thanks for the work, though. Same to you Hans.
I think I will mark this as closed, unless one of you has a brilliant idea. :)

But, yeah, I did mean that if data had AL in E, then all that row should go to sheet AL.
Same for the next row, etc. Once the value in E changed, write to the sheet with the related value (AZ, perhaps.)
 

·
Trusted Advisor
Joined
·
6,911 Posts
Go this for you.

Macro's named DoIt (got a buton on Sheet1 ans it creates a new sheet for every state.

My code allows for non state sorted lists.

If you run it again the data is appended, no test nothing to see if data is already prsent.

Just a quick and dirty.

The MakeSheet function is one use often, found a similir code and editted it to my needs, can do more that add sheets

Hope this helps.
 

Attachments

1 - 20 of 20 Posts
Status
Not open for further replies.
Top