Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PARSING DATA TO SHEETS
I have a master sheet with 240 columns representing data of each of the 240
branches. What macro could help in having 240 sheets be entered with first column of accounts and a unique column pertaining to the branch, i.e. from columns B and ahead? The Worksheets added so containing the name of the branch from the heading from the first row, B1 and ahead. Thanx in advance. -- Best Regards, Faraz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PARSING DATA TO SHEETS
Hi Faraz
If I understand you correctly you have a master sheet which is the first worksheet in your workbook. In this worksheet you have around 240 columns. Row 1 will have the branch name and from row2 onwards there are account numbers...What you are looking at is to have this split to 240 different sheets. The sheets are named after the branch name which appears in Row1 of the master sheet. The individual sheets should contain the account numbers in Col A and the branch name in Col B for each account number. Try the below macro with the master sheet (as below) Col A Col B Col C Col D Branch 1 Branch 2 Branch 3 Branch 4 1 2 3 4 1 2 3 4 2 3 4 3 4 4 Sub CreateSheets() Dim lngCols As Long, lngRows As Long Dim wSheet As Worksheet, wSheet1 As Worksheet Set wSheet = ActiveSheet lngCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column ActiveWorkbook.Sheets.Add After:=ActiveSheet, _ Count:=((lngCols + 1) - ActiveWorkbook.Sheets.Count) For intTemp = 2 To ActiveWorkbook.Sheets.Count Set wSheet1 = ActiveWorkbook.Sheets(intTemp) wSheet1.Name = wSheet.Cells(1, intTemp - 1) lngRows = wSheet.Cells(Rows.Count, intTemp - 1).End(xlUp).Row wSheet.Range(wSheet.Cells(2, intTemp - 1), wSheet.Cells(lngRows, _ intTemp - 1)).Copy wSheet1.Range("A1") wSheet1.Range("B1:B" & lngRows - 1) = wSheet.Cells(1, intTemp - 1) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a master sheet with 240 columns representing data of each of the 240 branches. What macro could help in having 240 sheets be entered with first column of accounts and a unique column pertaining to the branch, i.e. from columns B and ahead? The Worksheets added so containing the name of the branch from the heading from the first row, B1 and ahead. Thanx in advance. -- Best Regards, Faraz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PARSING DATA TO SHEETS
Nice 2 hear from you Jacob!
Been a longtime. However, the columns in the master sheet are 241, first one representing the account number. A two dimensional data like: Col A Col B Col C Col D Account Branch 1 Branch 2 Branch 3 Branch 4 1 2,000 750 48,510 5,455 2 3,200 514 464,586 55 3 250 789 785 5,698 4 0 654 616 7,895 Sample branch sheets would be: Col A Col B Account Branch 1 1 2,000 2 3,200 3 250 4 0 Col A Col B Account Branch 2 1 750 2 514 3 789 4 654 and so on. In other words, Column A is required on every sheet. Thanx again pal. -- Best Regards, Faraz "Jacob Skaria" wrote: Hi Faraz If I understand you correctly you have a master sheet which is the first worksheet in your workbook. In this worksheet you have around 240 columns. Row 1 will have the branch name and from row2 onwards there are account numbers...What you are looking at is to have this split to 240 different sheets. The sheets are named after the branch name which appears in Row1 of the master sheet. The individual sheets should contain the account numbers in Col A and the branch name in Col B for each account number. Try the below macro with the master sheet (as below) Col A Col B Col C Col D Branch 1 Branch 2 Branch 3 Branch 4 1 2 3 4 1 2 3 4 2 3 4 3 4 4 Sub CreateSheets() Dim lngCols As Long, lngRows As Long Dim wSheet As Worksheet, wSheet1 As Worksheet Set wSheet = ActiveSheet lngCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column ActiveWorkbook.Sheets.Add After:=ActiveSheet, _ Count:=((lngCols + 1) - ActiveWorkbook.Sheets.Count) For intTemp = 2 To ActiveWorkbook.Sheets.Count Set wSheet1 = ActiveWorkbook.Sheets(intTemp) wSheet1.Name = wSheet.Cells(1, intTemp - 1) lngRows = wSheet.Cells(Rows.Count, intTemp - 1).End(xlUp).Row wSheet.Range(wSheet.Cells(2, intTemp - 1), wSheet.Cells(lngRows, _ intTemp - 1)).Copy wSheet1.Range("A1") wSheet1.Range("B1:B" & lngRows - 1) = wSheet.Cells(1, intTemp - 1) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a master sheet with 240 columns representing data of each of the 240 branches. What macro could help in having 240 sheets be entered with first column of accounts and a unique column pertaining to the branch, i.e. from columns B and ahead? The Worksheets added so containing the name of the branch from the heading from the first row, B1 and ahead. Thanx in advance. -- Best Regards, Faraz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PARSING DATA TO SHEETS
Hi again; the requirement is even simpler...now..try the below
Sub CreateSheets() Dim lngCols As Long Dim wSheet As Worksheet, wSheet1 As Worksheet Set wSheet = ActiveSheet lngCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column ActiveWorkbook.Sheets.Add After:=ActiveSheet, _ Count:=((lngCols) - ActiveWorkbook.Sheets.Count) For intTemp = 2 To ActiveWorkbook.Sheets.Count Set wSheet1 = ActiveWorkbook.Sheets(intTemp) wSheet1.Name = wSheet.Cells(1, intTemp) wSheet.Columns(1).Copy wSheet1.Range("A1") wSheet.Columns(intTemp).Copy wSheet1.Range("B1") Next End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Nice 2 hear from you Jacob! Been a longtime. However, the columns in the master sheet are 241, first one representing the account number. A two dimensional data like: Col A Col B Col C Col D Account Branch 1 Branch 2 Branch 3 Branch 4 1 2,000 750 48,510 5,455 2 3,200 514 464,586 55 3 250 789 785 5,698 4 0 654 616 7,895 Sample branch sheets would be: Col A Col B Account Branch 1 1 2,000 2 3,200 3 250 4 0 Col A Col B Account Branch 2 1 750 2 514 3 789 4 654 and so on. In other words, Column A is required on every sheet. Thanx again pal. -- Best Regards, Faraz "Jacob Skaria" wrote: Hi Faraz If I understand you correctly you have a master sheet which is the first worksheet in your workbook. In this worksheet you have around 240 columns. Row 1 will have the branch name and from row2 onwards there are account numbers...What you are looking at is to have this split to 240 different sheets. The sheets are named after the branch name which appears in Row1 of the master sheet. The individual sheets should contain the account numbers in Col A and the branch name in Col B for each account number. Try the below macro with the master sheet (as below) Col A Col B Col C Col D Branch 1 Branch 2 Branch 3 Branch 4 1 2 3 4 1 2 3 4 2 3 4 3 4 4 Sub CreateSheets() Dim lngCols As Long, lngRows As Long Dim wSheet As Worksheet, wSheet1 As Worksheet Set wSheet = ActiveSheet lngCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column ActiveWorkbook.Sheets.Add After:=ActiveSheet, _ Count:=((lngCols + 1) - ActiveWorkbook.Sheets.Count) For intTemp = 2 To ActiveWorkbook.Sheets.Count Set wSheet1 = ActiveWorkbook.Sheets(intTemp) wSheet1.Name = wSheet.Cells(1, intTemp - 1) lngRows = wSheet.Cells(Rows.Count, intTemp - 1).End(xlUp).Row wSheet.Range(wSheet.Cells(2, intTemp - 1), wSheet.Cells(lngRows, _ intTemp - 1)).Copy wSheet1.Range("A1") wSheet1.Range("B1:B" & lngRows - 1) = wSheet.Cells(1, intTemp - 1) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a master sheet with 240 columns representing data of each of the 240 branches. What macro could help in having 240 sheets be entered with first column of accounts and a unique column pertaining to the branch, i.e. from columns B and ahead? The Worksheets added so containing the name of the branch from the heading from the first row, B1 and ahead. Thanx in advance. -- Best Regards, Faraz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text parsing - Extracting data from inconsistent data entry format. | Excel Programming | |||
Parsing into different sheets | Excel Programming | |||
Parsing data into seperate sheets | Excel Programming | |||
Parsing Data | Excel Programming | |||
Parsing Data | Excel Programming |