PARSING DATA TO SHEETS

[Faraz A. Qureshi]

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.

 

[Jacob Skaria]

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

 

[Faraz A. Qureshi]

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.

 

[Jacob Skaria]

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