Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro For Breaking Data To Different Sheets
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro For Breaking Data To Different Sheets
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro For Breaking Data To Different Sheets
See also
http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "FARAZ QURESHI" wrote in message ... 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 __________ Information from ESET Smart Security, version of virus signature database 3952 (20090320) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compiling data from multiple Excel sheets - macro? | Excel Worksheet Functions | |||
macro to print sheets from wrkbk only if data has been typed in | Excel Discussion (Misc queries) | |||
Sorting Worksheet w/o breaking links to other sheets | Excel Discussion (Misc queries) | |||
Breaking out data in a cell | Excel Worksheet Functions | |||
breaking during a macro run | Excel Discussion (Misc queries) |