Chop Worksheet!

Currently i'm using Cut & Paste to cut sections from a massive excel worksheet & then pasting the sections into new excel worksheets & renaming the sheets.

Does anyone know how i can chop my excel worksheet into multiple sheets automatically?

For example. Column A contains hundreds of country codes. The first 80 rows contain country code USA.

Then row 81 to 141 change to country code IRE & so on....

When the country code changes i need to chop those country rows into a new worksheet.

Id appreciate anyones help,
thanks

Moved To Office Development

VBA Code, loop, variables that point to sheets (like new sheets)...

I am not quite grasping what your wanting here: Are there variable rows?

This copies the range you talked about to a newly created sheet called countries:

VB Code:

Option Explicit
 
Sub ChopiT()
    Dim wASht As Worksheet
    Set wASht = ActiveSheet
    
    Sheets.Add
    On Error GoTo ErrH
    ActiveSheet.Name = "Countries"
    
    wASht.Range("A81:A141").Copy Sheets("Countries").Range("A1")
    
ErrExit:
    Set wASht = Nothing
    Exit Sub
ErrH:
    MsgBox Err.Description
    GoTo ErrExit
End Sub