In this post I am going to show how to create a new sheet for each airplane using vba. The macro copies airplane and model values into each new sheet.

Before:

After:

The Code

Option Explicit

Sub Splitdatatosheets()
'
' Splitdatatosheets Macro
'

'
Dim rng As Range

Dim rng1 As Range

Dim vrb As Boolean

Dim sht As Worksheet

Set rng = Sheets("Sheet1").Range("A4")

Set rng1 = Sheets("Sheet1").Range("A4:D4")

vrb = False

Do While rng <> ""

For Each sht In Worksheets

If sht.Name = rng.Value Then

sht.Select

Range("A2").Select

Do While Selection <> ""

ActiveCell.Offset(1, 0).Activate

Loop

rng1.Copy ActiveCell

ActiveCell.Offset(1, 0).Activate

Set rng1 = rng1.Offset(1, 0)

Set rng = rng.Offset(1, 0)

vrb = True

End If

Next sht

If vrb = False Then

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = rng.Value

Sheets("Sheet1").Range("A3:B3").Copy ActiveSheet.Range("A1")

Range("A2").Select

Do While Selection <> ""

ActiveCell.Offset(1, 0).Activate

Loop

rng1.Copy ActiveCell

Set rng1 = rng1.Offset(1, 0)

Set rng = rng.Offset(1, 0)

End If

vrb = False

Loop

End Sub

Download excel tutorial file

Remember to enable macros and backup your excel file because you can´t undo macros.

Split data across multiple sheets.xls
(Excel 97-2003  Workbook *.xls)

Related posts:

1. Consolidate sheets in excel (vba)
2. Categorize values into multiple columns using vba in excel
3. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
4. Combine data from multiple sheets in excel
5. Count unique distinct numbers across multiple sheets (3D range) in excel
6. Search for multiple text strings in multiple cells and use in data validation in excel
7. Create a unique distinct list of a long list without sacrificing performance using vba in excel
8. Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
9. Scan stock markets in excel
10. Create a dynamic stock chart using a web query and a drop down list in excel