Macro For Breaking Data To Different Sheets

  • Thread starter Thread starter FARAZ QURESHI
  • Start date Start date
F

FARAZ QURESHI

I have a sheet with consolidated data as follows:
Region Branch Case Amount Date

Can an expert friend devise me a code to sort the data branch wise and then
create different sheets for every branch?

Thanx in advance!
 
To enter the macro
Open your workbook
Rename the sheet with data as DATA
Press ALT-F11
Insert Module
Paste the Code
To run press F5
Warning: It will rewrite any sheets with names matching any branch (assumed
to be in Col B).
It will create a sheet for every branch name and copy data there...

Option Base 1
Sub distribute()
'Region Branch Case Amount Date
Dim j As Integer
j = 1
Dim lastRow As Long
Dim sourceSheet As Worksheet
Dim currentSheet As String
Dim sheetName As String
Dim currentRow() As Long

Set sourceSheet = Worksheets("Data")
With sourceSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

ReDim currentRow(Sheets.Count)
For i = 1 To Sheets.Count
currentRow(i) = 2
Next

For i = 2 To lastRow
flag = True
currentSheet = sourceSheet.Cells(i, 2)
j = 0
For Each ws In Worksheets
j = j + 1
If ws.Name = currentSheet Then
flag = False
Exit For
End If
Next ws
If flag Then
Worksheets.Add After:=Sheets(Sheets.Count)
j = Sheets.Count
Worksheets(j).Name = currentSheet
ReDim Preserve currentRow(j)
currentRow(j) = 2
End If
sourceSheet.Cells(i, 1).EntireRow.Copy _
Destination:=Worksheets(j).Cells(currentRow(j), 1)
currentRow(j) = currentRow(j) + 1
Next i
'MsgBox currentRow(15)
End Sub
 
See also
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




FARAZ QURESHI said:
I have a sheet with consolidated data as follows:
Region Branch Case Amount Date

Can an expert friend devise me a code to sort the data branch wise and then
create different sheets for every branch?

Thanx in advance!

--
Best Regards,
FARAZ A. QURESHI

__________ Information from ESET Smart Security, version of virus signature database 3952 (20090320) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...


__________ Information from ESET Smart Security, version of virus signature database 3952 (20090320) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
XClent!!!!
However,
1. The headers are not being copied to the new sheet; &
2. The main Data sheet remains to be unsorted.

Any idea?
 
Back
Top