Separate total data into separate worksheets

[ProjectA]

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?

 

[Andrew Poulsom]

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]

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]

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]

This seems to work:

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]

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]

I like Walkenbach's VBA book - the big one not the Dummies version. You can buy it here.