Excel 2003 Split spreadsheet to different tabs

[matthewR]

I have a spreadsheet that I would like to break out into different tabs. Column A has data like 1, 2-9,10-19,20-49 etc. I have it sorted so that all 1s are together, all 2-9 are together etc. I have to put each grouping on a different tab with the name of that grouping on the tab. I have 10 tabs. Is there an easy way to do this (maybe a macro) that would split this worksheet and put the data (according to size range) on different tabs, naming the tabs accordingly? Also, can the original spreadsheet be left in tact.

[HansV]

Here is a macro you can use. I have assumed that row 1 contains column headers.

Code: Select all

Public Sub Separate()
  Dim wshSource As Worksheet
  Dim wshTarget As Worksheet
  Dim r As Long
  Dim m As Long
  Dim t As Long
  
  Application.ScreenUpdating = False
  Set wshSource = ActiveSheet
  ' Optional: sort column A
  'wshSource.Range("A1").Sort Key1:=wshSource.Range("A1"), Order1:=xlAscending, Header:=xlYes
  m = wshSource.Range("A" & wshSource.Rows.Count).End(xlUp).Row
  For r = 2 To m
    If wshSource.Range("A" & r) <> wshSource.Range("A" & (r - 1)) Then
      Set wshTarget = Worksheets.Add(After:=Worksheets(Worksheets.Count))
      wshTarget.Name = wshSource.Range("A" & r)
      wshSource.Rows(1).Copy Destination:=wshTarget.Range("A1")
      t = 1
    End If
    t = t + 1
    wshSource.Rows(r).Copy Destination:=wshTarget.Range("A" & t)
  Next r
  wshSource.Activate
  Application.ScreenUpdating = True
End Sub

[matthewR]

I placed the macro in my personal.xls sheet but I don't see it when I want to run it. What is the difference between a public sub and just a sub? Where do I put public subs? Sorry for the dumb questions.

[matthewR]

I found it - it had module in front of it. Worked perfectly. Thanks for the help....