PERFECT!!! This is exactly what I'm looking for. Thank you so much for helping me with this!Try this (added code in red)
Rich (BB code):Sub SalesmanToSheet() Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long Dim ws As Worksheet Application.ScreenUpdating = False With ActiveSheet Application.DisplayAlerts = False For Each ws In ThisWorkbook.Worksheets If ws.Name <> .Name Then ws.Delete Next ws Application.DisplayAlerts = True lastrow = .Cells(Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("A2"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom iStart = 2 For i = 2 To lastrow If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then iEnd = i Sheets.Add after:=Sheets(Sheets.Count) Set ws = ActiveSheet On Error Resume Next ws.Name = .Range("A" & iStart).Value On Error GoTo 0 ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value With ws.Rows(1) .HorizontalAlignment = xlCenter With .Font .ColorIndex = 5 .Bold = True End With End With .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2") iStart = iEnd + 1 End If Next i End With Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
There is a more versatile version of this code here http://www.mrexcel.com/forum/showthread.php?t=396069
I do want to sum the demographic columns by zipcode but want the totals to show in their own worksheet. So for example zipode 29690 will have its own tab with one line of totals. Does this make sense?Do you mean that you want to add totals at the bottom of some of the columns? If so, which columns?
Sorry, It's probably the way I'm trying to explain it to you. I'm basically looking for the same type of macro you gave me yesterday that splits the data into new worksheets based off of a column. In this case the column would be the zipcode instead of the route number. The zipcode is in column C. I have demographic fields that are populated with numbers. So for example....In my master spreadsheet I have 50 addresses in the zipcode 29690 and there is a demographic field called children...indicating how many children live at that address. If each address has one child living there then I would have a new worksheet created for 29690 with the children column summed to 50.No sorry, I'm not getting it.
Try posting samples of what you have and what you want using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
Sub ZipsToSheet()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet, j As Integer
Application.ScreenUpdating = False
With ActiveSheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> .Name Then ws.Delete
Next ws
Application.DisplayAlerts = True
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("C2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To lastrow
If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then
iEnd = i
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = .Range("C" & iStart).Value
On Error GoTo 0
ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
With ws.Rows(1)
.HorizontalAlignment = xlCenter
With .Font
.ColorIndex = 5
.Bold = True
End With
End With
For j = 1 To 4
ws.Cells(2, j).Value = .Cells(iStart, j).Value
Next j
For j = 5 To 38
If j <> 8 And j <> 9 Then
ws.Cells(2, j).Value = WorksheetFunction.Sum(.Range(.Cells(iStart, j), .Cells(iEnd, j)))
End If
Next j
iStart = iEnd + 1
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub