Separate total data into separate worksheets

ProjectA

New Member
Joined
Sep 17, 2002
Messages
11
I have a worksheet that has a column of two character country codes I wish to sort down. For every change in country code I need a separate worksheet in the same workbook. The name of the worksheet should be the two character country code.

Is there a way of doing this in VB?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Yes, but:

1. Do you want to transfer the data to the new worksheets?
2. If so how many columns?
3. If so do you want to delete the original sheet or data?
 

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
Personally I would create a pivot with country code in the row section and count of country code in the data section, you could then just double click on the count for each code and a sheet with that codes data on would be created, then its just a case of formatting and renaming the sheet.

But then I like pivots...and I don't mind a couple of hours monotony. It depends if you want the sheets to automatically update.
 

ProjectA

New Member
Joined
Sep 17, 2002
Messages
11
I would like to transfer the data to the new worksheets, but keep the original in sheet one.

There are 19 colunms (A-S inclusive) in total and the country code lies in column 2 (B).

I know all about the pivot table methods and data>sort>sub total tools. Its just not convenient in this case (i.e. the boss wants it so!!)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

This seems to work:

Code:
Sub Test()
    Dim Rng As Range
    Dim ShSource As Worksheet
    Dim x As Long
    Dim c As Range
    Dim ShName As String
'   *** Change name of original worksheet to suit ***
    Set ShSource = Worksheets("Sheet1")
'   *** Change starting row in column B to suit ***
    Set Rng = ShSource.Range("B1:B" & Range("B65536").End(xlUp).Row)
    x = 1
    Application.ScreenUpdating = False
    For Each c In Rng
        If c.Offset(1, 0) <> c.Value Then
            ShName = c.Text
            Range(Rng.Rows(x), c.EntireRow).Copy
            Worksheets.Add After:=Sheets(Sheets.Count)
            With ActiveSheet
                .Paste
                .Name = ShName
                .Range("A1").Select
            End With
            ShSource.Activate
            x = c.Row + 1
        End If
    Next c
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

As always, make sure you back up your existing workbook first.
 

ProjectA

New Member
Joined
Sep 17, 2002
Messages
11
Christ!! That's a bit quick. Runs like a dream. Thanks alot for that.

Any suggestions on learning whizzy VB stuff. I have tried reading the help files and the QUE book on Excel and VBA. I have got as far as recording macros and editing them, but no where near creating my own.

Thanks again
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I like Walkenbach's VBA book - the big one not the Dummies version. You can buy it here.
 

Forum statistics

Threads
1,147,818
Messages
5,743,378
Members
423,791
Latest member
kevinlee_5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top