Macro For Breaking Data To Different Sheets
XClent!!!!
However,
1. The headers are not being copied to the new sheet; &
2. The main Data sheet remains to be unsorted.
Any idea?
--
Best Regards,
FARAZ A. QURESHI
"Sheeloo" wrote:
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
"FARAZ QURESHI" wrote:
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
|