Excel 2003 Split spreadsheet to different tabs
-
- 5StarLounger
- Posts: 627
- Joined: 03 Feb 2010, 15:02
Excel 2003 Split spreadsheet to different tabs
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.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel 2003 Split spreadsheet to different tabs
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 627
- Joined: 03 Feb 2010, 15:02
Re: Excel 2003 Split spreadsheet to different tabs
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.
-
- 5StarLounger
- Posts: 627
- Joined: 03 Feb 2010, 15:02
Re: Excel 2003 Split spreadsheet to different tabs
I found it - it had module in front of it. Worked perfectly. Thanks for the help....