| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample fr the followings...
Hi guys,
I am newbie when it comes to programming in excel... Currently I have an excel worksheet with 4 sheets in it. All four sheets contains data and based on them I am creating more sheet by looking up data from them. Way I am looking up data is follows: Based on column one in first sheet I am creating more sheets (e.g., If i have a in A1, b in A2, c in A3 and so on...) My new sheets names that I am creatin is based on this. So I have Sheet a, b, c ...and so on. How do i programmatically creating this sheets based on the scenario I definded above how do i know when to end creating sheets? Once I created these sheets now I have to move data to them. on my second sheet I have title column and next to it data columns that has the sheet names that I created in step 1 above (a,b,c, so on...), e,g a, b, c, d, e, f Title 1, 1, 0, 1, 0,1 Title 2, 0,1,1,0,0,1 Title 3, 1,1,0,0,0,0 no Every column that has value 1 in it moves the title of that sheet to its appropriate sheet. So in case of above: Title 1 and title 3 moves to sheet a, and so on... How can I programetically do this. I appriciate an help you guys can provide. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sample fr the followings...
This code will create the new worksheets and add the title Headers to the
columns according to sheet 2. Didn't know wha data needed to be copied. I did a lot of error checking to make sure no duplicate worksheets were created and that the title columns were not duplicated. Most of the code are these checks. the code would be much simplier if I didn't do these checks. but you said you already had 4 worksheets and didn't want to over-write or duplicate information that already existed. Sub CreateNewSheets() Sh1Name = "Sheet1" Sh2Name = "Sheet2" Worksheets(Sh1Name).Activate Sh1LastRow = Worksheets(Sh1Name).Cells(Rows.Count, 1). _ End(xlUp).Row Set Sh1Range = Worksheets(Sh1Name).Range(Cells(1, 1), _ Cells(Sh1LastRow, 1)) For Each Sh1Cells In Sh1Range 'Error check - make sure Sheet 1 isn't in list of sheets If (StrComp(Sh1Name, Sh1Cells) < 0) Then 'Check if Sheet exists found = False For Each Whs In ThisWorkbook.Worksheets If (StrComp(StrConv(Sh1Cells, vbUpperCase), _ StrConv(Whs.Name, vbUpperCase)) = 0) Then found = True Exit For End If Next Whs If found = False Then Sheets.Add Sheets(ActiveSheet.Name).Name = Sh1Cells End If End If Next Sh1Cells 'Create column titles on each worksheet Worksheets(Sh2Name).Activate Sh2LastColumn = Worksheets(Sh2Name).Cells(1, Columns.Count). _ End(xlToLeft).Column Sh2LastRow = Worksheets(Sh2Name).Cells(Rows.Count, 1). _ End(xlUp).Row Set Sh2ColRange = Worksheets(Sh2Name). _ Range(Cells(1, 2), _ Cells(1, Sh2LastColumn)) For Each Sh2ColCells In Sh2ColRange Worksheets(Sh2Name).Activate Set Sh2RowRange = Worksheets(Sh2Name). _ Range(Cells(2, Sh2ColCells.Column), _ Cells(Sh2LastRow, Sh2ColCells.Column)) SheetName = Cells(1, Sh2ColCells.Column) Worksheets(SheetName).Activate For Each Sh2RowCells In Sh2RowRange If Sh2RowCells = 1 Then 'Check if title exists before adding Sh2Xtitle = Worksheets(Sh2Name).Cells(Sh2RowCells.Row, 1) found = False If Not IsEmpty(Worksheets(SheetName).Cells(1, 1)) Then ShXLastColumn = Worksheets(SheetName). _ Cells(1, Columns.Count).End(xlToLeft).Column Set ShXtitleRange = Worksheets(SheetName). _ Range(Cells(1, 1), _ Cells(1, ShXLastColumn)) For Each ShXtitle In ShXtitleRange If (StrComp(ShXtitle, _ Worksheets(Sh2Name).Cells(Sh2RowCells.Row, 1)) = 0) Then found = True Exit For End If Next ShXtitle If found = False Then Worksheets(SheetName).Cells(1, ShXLastColumn + 1) = _ Sh2Xtitle End If Else Worksheets(SheetName).Cells(1, 1) = Sh2Xtitle End If End If Next Sh2RowCells Next Sh2ColCells End Sub "kashif afzaal" wrote: Hi guys, I am newbie when it comes to programming in excel... Currently I have an excel worksheet with 4 sheets in it. All four sheets contains data and based on them I am creating more sheet by looking up data from them. Way I am looking up data is follows: Based on column one in first sheet I am creating more sheets (e.g., If i have a in A1, b in A2, c in A3 and so on...) My new sheets names that I am creatin is based on this. So I have Sheet a, b, c ...and so on. How do i programmatically creating this sheets based on the scenario I definded above how do i know when to end creating sheets? Once I created these sheets now I have to move data to them. on my second sheet I have title column and next to it data columns that has the sheet names that I created in step 1 above (a,b,c, so on...), e,g a, b, c, d, e, f Title 1, 1, 0, 1, 0,1 Title 2, 0,1,1,0,0,1 Title 3, 1,1,0,0,0,0 no Every column that has value 1 in it moves the title of that sheet to its appropriate sheet. So in case of above: Title 1 and title 3 moves to sheet a, and so on... How can I programetically do this. I appriciate an help you guys can provide. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sample fr the followings...
Thanks for your help joel.
Is it ok if i send you my excel sheet and maybe you can see the data and its output and help me more. Let me know your email address and I will send it. Thanks again for your help. "Joel" wrote: This code will create the new worksheets and add the title Headers to the columns according to sheet 2. Didn't know wha data needed to be copied. I did a lot of error checking to make sure no duplicate worksheets were created and that the title columns were not duplicated. Most of the code are these checks. the code would be much simplier if I didn't do these checks. but you said you already had 4 worksheets and didn't want to over-write or duplicate information that already existed. Sub CreateNewSheets() Sh1Name = "Sheet1" Sh2Name = "Sheet2" Worksheets(Sh1Name).Activate Sh1LastRow = Worksheets(Sh1Name).Cells(Rows.Count, 1). _ End(xlUp).Row Set Sh1Range = Worksheets(Sh1Name).Range(Cells(1, 1), _ Cells(Sh1LastRow, 1)) For Each Sh1Cells In Sh1Range 'Error check - make sure Sheet 1 isn't in list of sheets If (StrComp(Sh1Name, Sh1Cells) < 0) Then 'Check if Sheet exists found = False For Each Whs In ThisWorkbook.Worksheets If (StrComp(StrConv(Sh1Cells, vbUpperCase), _ StrConv(Whs.Name, vbUpperCase)) = 0) Then found = True Exit For End If Next Whs If found = False Then Sheets.Add Sheets(ActiveSheet.Name).Name = Sh1Cells End If End If Next Sh1Cells 'Create column titles on each worksheet Worksheets(Sh2Name).Activate Sh2LastColumn = Worksheets(Sh2Name).Cells(1, Columns.Count). _ End(xlToLeft).Column Sh2LastRow = Worksheets(Sh2Name).Cells(Rows.Count, 1). _ End(xlUp).Row Set Sh2ColRange = Worksheets(Sh2Name). _ Range(Cells(1, 2), _ Cells(1, Sh2LastColumn)) For Each Sh2ColCells In Sh2ColRange Worksheets(Sh2Name).Activate Set Sh2RowRange = Worksheets(Sh2Name). _ Range(Cells(2, Sh2ColCells.Column), _ Cells(Sh2LastRow, Sh2ColCells.Column)) SheetName = Cells(1, Sh2ColCells.Column) Worksheets(SheetName).Activate For Each Sh2RowCells In Sh2RowRange If Sh2RowCells = 1 Then 'Check if title exists before adding Sh2Xtitle = Worksheets(Sh2Name).Cells(Sh2RowCells.Row, 1) found = False If Not IsEmpty(Worksheets(SheetName).Cells(1, 1)) Then ShXLastColumn = Worksheets(SheetName). _ Cells(1, Columns.Count).End(xlToLeft).Column Set ShXtitleRange = Worksheets(SheetName). _ Range(Cells(1, 1), _ Cells(1, ShXLastColumn)) For Each ShXtitle In ShXtitleRange If (StrComp(ShXtitle, _ Worksheets(Sh2Name).Cells(Sh2RowCells.Row, 1)) = 0) Then found = True Exit For End If Next ShXtitle If found = False Then Worksheets(SheetName).Cells(1, ShXLastColumn + 1) = _ Sh2Xtitle End If Else Worksheets(SheetName).Cells(1, 1) = Sh2Xtitle End If End If Next Sh2RowCells Next Sh2ColCells End Sub "kashif afzaal" wrote: Hi guys, I am newbie when it comes to programming in excel... Currently I have an excel worksheet with 4 sheets in it. All four sheets contains data and based on them I am creating more sheet by looking up data from them. Way I am looking up data is follows: Based on column one in first sheet I am creating more sheets (e.g., If i have a in A1, b in A2, c in A3 and so on...) My new sheets names that I am creatin is based on this. So I have Sheet a, b, c ...and so on. How do i programmatically creating this sheets based on the scenario I definded above how do i know when to end creating sheets? Once I created these sheets now I have to move data to them. on my second sheet I have title column and next to it data columns that has the sheet names that I created in step 1 above (a,b,c, so on...), e,g a, b, c, d, e, f Title 1, 1, 0, 1, 0,1 Title 2, 0,1,1,0,0,1 Title 3, 1,1,0,0,0,0 no Every column that has value 1 in it moves the title of that sheet to its appropriate sheet. So in case of above: Title 1 and title 3 moves to sheet a, and so on... How can I programetically do this. I appriciate an help you guys can provide. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sample fr the followings...
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sample fr the followings...
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I random sample from a set until every sample is selected? | Excel Discussion (Misc queries) | |||
| sample program | Charts and Charting in Excel | |||
| random sample | Excel Worksheet Functions | |||
| Sample | Excel Discussion (Misc queries) | |||
| TOM O , sample is sent | Excel Programming | |||
Hybrid Mode
