Sometimes it’s quite required to have separate sheets for each of the data types.

I want separate sheets for each region like East, Central etc.

OrderDate Region Rep Item Units UnitCost Total
1/6/2016 East Jones Pencil 95 1.99 189.05
1/23/2016 Central Kivell Binder 50 19.99 999.50
2/9/2016 Central Jardine Pencil 36 4.99 179.64
2/26/2016 Central Gill Pen 27 19.99 539.73
3/15/2016 West Sorvino Pencil 56 2.99 167.44
4/1/2016 East Jones Binder 60 4.99 299.40
4/18/2016 Central Andrews Pencil 75 1.99 149.25
5/5/2016 Central Jardine Pencil 90 4.99 449.10
5/22/2016 West Thompson Pencil 32 1.99 63.68
6/8/2016 East Jones Binder 60 8.99 539.40
6/25/2016 Central Morgan Pencil 90 4.99 449.10
7/12/2016 East Howard Binder 29 1.99 57.71
7/29/2016 East Parent Binder 81 19.99 1,619.19
8/15/2016 East Jones Pencil 35 4.99 174.65
9/1/2016 Central Smith Desk 2 125.00 250.00
9/18/2016 East Jones Pen Set 16 15.99 255.84

Lets start with coding.

I am assuming you can create a Pivot table with the data and is on a sheet. Name of pivot sheet does not matter.

pivot-1

Now here is the code and the file is attached.

‘————————————————————–

Sub Macro1()

Dim mysheet As Worksheet

Set mysheet = ActiveWorkbook.ActiveSheet

lastRow = mysheet.UsedRange.Rows.Count
For i = 5 To lastRow
‘ Save for the sheet name
myname = Range(“A” & i).Value

‘ Select the range and the do a double click like function
Range(“B” & i).Select
Application.CutCopyMode = False
Selection.ShowDetail = True

‘ Change the name of sheet for each region

ActiveSheet.Name = myname

‘ Select the sheet back
mysheet.Select

Next i

End Sub

‘————————————————————–

Output:-

ouptut_sheets

make-new-sheets-vba

Tip: In the pivot change the field from Region to Item or Rep and see sheets created. For brave, there is a code that asks for a folder and saves them as separate sheets.

Cheers.

CuriousJatin

Advertisements