Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary worksheet
Although this might be better handled with a true database - like Access,
Excel is the only choice I have now. My workbook contains 50+ worksheets labeled 101 to 150. More worksheets will likely be added. Each worksheet contains data on a single individual.. I want Excel to enter data on a summary worksheet (in the same workbook) from each worksheet. Specifically: Cell I9 on every worksheet is the status of he person (active,inactive, etc). Cells C6, D6 and N6 on each sheet contain First names, Last Names, and ID on each person. So if cell I9 status is "active" I want cells B2 - B50(or more if sheets are added) on the new summary sheet to contain the data in C6 of every sheet. C2-C50 would contain data in D6 of each sheet, D2-D50 would contain data in N6 of each sheet. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary worksheet
Sub Doit()
Dim wSheet As Worksheet Dim X As Double Dim DataArray(500, 10) As Variant Dim NewWks As Worksheet Dim TheStr As String Sheets("Summary").Select ActiveWindow.SelectedSheets.Delete For Each wSheet In Worksheets TheStr = wSheet.Name Sheets(TheStr).Select X = X + 1 DataArray(X, 1) = Range("I9") DataArray(X, 2) = Range("C6") 'First Name DataArray(X, 3) = Range("D6") 'Last Name DataArray(X, 4) = Range("N6") ' ID Next wSheet Set NewWks = Worksheets.Add NewWks.Name = "Summary" Sheets("Summary").Select For Y = 1 To X For Z = 1 To 4 Cells(Y + 1, Z + 1).Value = DataArray(Y, Z) Next Next Cells(1, 3).Value = "FName" Cells(1, 4).Value = "LName" Cells(1, 2).Value = "Status" Cells(1, 5).Value = "ID" Cells.Select Cells.EntireColumn.AutoFit Set PrtRng = Range(Cells(1, 1), Cells(5, X + 3)) With ActiveSheet.PageSetup .Zoom = False .PrintArea = PrtRng.Address .PrintTitleRows = "$1:$1" .Orientation = xlPortrait .FitToPagesWide = 1 .FitToPagesTall = 10 End With Cells(2, 1).Select ActiveWindow.FreezePanes = True End Sub "HH" wrote: Although this might be better handled with a true database - like Access, Excel is the only choice I have now. My workbook contains 50+ worksheets labeled 101 to 150. More worksheets will likely be added. Each worksheet contains data on a single individual.. I want Excel to enter data on a summary worksheet (in the same workbook) from each worksheet. Specifically: Cell I9 on every worksheet is the status of he person (active,inactive, etc). Cells C6, D6 and N6 on each sheet contain First names, Last Names, and ID on each person. So if cell I9 status is "active" I want cells B2 - B50(or more if sheets are added) on the new summary sheet to contain the data in C6 of every sheet. C2-C50 would contain data in D6 of each sheet, D2-D50 would contain data in N6 of each sheet. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary worksheet
Mike,
Thanks for your help. I not great at this but I assume I need to insert a sheet labeled "Summary" right click on the tap, and insert the code. If so nothing happened. I'm sure I need to do something else. Side questions: If I insert another sheet to draw different data will that affect the summany sheet code? i.e. will the code you gave try to draw from the new sheet? Hank "Mike H." wrote in message ... Sub Doit() Dim wSheet As Worksheet Dim X As Double Dim DataArray(500, 10) As Variant Dim NewWks As Worksheet Dim TheStr As String Sheets("Summary").Select ActiveWindow.SelectedSheets.Delete For Each wSheet In Worksheets TheStr = wSheet.Name Sheets(TheStr).Select X = X + 1 DataArray(X, 1) = Range("I9") DataArray(X, 2) = Range("C6") 'First Name DataArray(X, 3) = Range("D6") 'Last Name DataArray(X, 4) = Range("N6") ' ID Next wSheet Set NewWks = Worksheets.Add NewWks.Name = "Summary" Sheets("Summary").Select For Y = 1 To X For Z = 1 To 4 Cells(Y + 1, Z + 1).Value = DataArray(Y, Z) Next Next Cells(1, 3).Value = "FName" Cells(1, 4).Value = "LName" Cells(1, 2).Value = "Status" Cells(1, 5).Value = "ID" Cells.Select Cells.EntireColumn.AutoFit Set PrtRng = Range(Cells(1, 1), Cells(5, X + 3)) With ActiveSheet.PageSetup .Zoom = False .PrintArea = PrtRng.Address .PrintTitleRows = "$1:$1" .Orientation = xlPortrait .FitToPagesWide = 1 .FitToPagesTall = 10 End With Cells(2, 1).Select ActiveWindow.FreezePanes = True End Sub "HH" wrote: Although this might be better handled with a true database - like Access, Excel is the only choice I have now. My workbook contains 50+ worksheets labeled 101 to 150. More worksheets will likely be added. Each worksheet contains data on a single individual.. I want Excel to enter data on a summary worksheet (in the same workbook) from each worksheet. Specifically: Cell I9 on every worksheet is the status of he person (active,inactive, etc). Cells C6, D6 and N6 on each sheet contain First names, Last Names, and ID on each person. So if cell I9 status is "active" I want cells B2 - B50(or more if sheets are added) on the new summary sheet to contain the data in C6 of every sheet. C2-C50 would contain data in D6 of each sheet, D2-D50 would contain data in N6 of each sheet. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary worksheet
Ok Mike,
I figured out where to put the code (module right). It works great except for a couple of things. 1. The summary sheet should contain list of people that are "Active" in cell I9 on each worksheet. 2. How can I add another worksheet without it being involved in the Summary sheet? I think if we solve #1 and I dont have the word "Active" in any other sheet it won't be in the summary. Or is there a better way. Thanks for your help "HH" wrote in message ... Mike, Thanks for your help. I not great at this but I assume I need to insert a sheet labeled "Summary" right click on the tap, and insert the code. If so nothing happened. I'm sure I need to do something else. Side questions: If I insert another sheet to draw different data will that affect the summany sheet code? i.e. will the code you gave try to draw from the new sheet? Hank "Mike H." wrote in message ... Sub Doit() Dim wSheet As Worksheet Dim X As Double Dim DataArray(500, 10) As Variant Dim NewWks As Worksheet Dim TheStr As String Sheets("Summary").Select ActiveWindow.SelectedSheets.Delete For Each wSheet In Worksheets TheStr = wSheet.Name Sheets(TheStr).Select X = X + 1 DataArray(X, 1) = Range("I9") DataArray(X, 2) = Range("C6") 'First Name DataArray(X, 3) = Range("D6") 'Last Name DataArray(X, 4) = Range("N6") ' ID Next wSheet Set NewWks = Worksheets.Add NewWks.Name = "Summary" Sheets("Summary").Select For Y = 1 To X For Z = 1 To 4 Cells(Y + 1, Z + 1).Value = DataArray(Y, Z) Next Next Cells(1, 3).Value = "FName" Cells(1, 4).Value = "LName" Cells(1, 2).Value = "Status" Cells(1, 5).Value = "ID" Cells.Select Cells.EntireColumn.AutoFit Set PrtRng = Range(Cells(1, 1), Cells(5, X + 3)) With ActiveSheet.PageSetup .Zoom = False .PrintArea = PrtRng.Address .PrintTitleRows = "$1:$1" .Orientation = xlPortrait .FitToPagesWide = 1 .FitToPagesTall = 10 End With Cells(2, 1).Select ActiveWindow.FreezePanes = True End Sub "HH" wrote: Although this might be better handled with a true database - like Access, Excel is the only choice I have now. My workbook contains 50+ worksheets labeled 101 to 150. More worksheets will likely be added. Each worksheet contains data on a single individual.. I want Excel to enter data on a summary worksheet (in the same workbook) from each worksheet. Specifically: Cell I9 on every worksheet is the status of he person (active,inactive, etc). Cells C6, D6 and N6 on each sheet contain First names, Last Names, and ID on each person. So if cell I9 status is "active" I want cells B2 - B50(or more if sheets are added) on the new summary sheet to contain the data in C6 of every sheet. C2-C50 would contain data in D6 of each sheet, D2-D50 would contain data in N6 of each sheet. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary worksheet
I am not sure I understand your questions. How can you ahve a list in one
cell, I9? I don't get it???? Also, the way I set the macro up is: 1. Delete the tab, Summary. 2. Collect all the data from every tab left in the sheet 3. create a new summary and put entries on it. So to answer your question, unless I modified the code, all sheets, other than summary, are picked up. "HH" wrote: Ok Mike, I figured out where to put the code (module right). It works great except for a couple of things. 1. The summary sheet should contain list of people that are "Active" in cell I9 on each worksheet. 2. How can I add another worksheet without it being involved in the Summary sheet? I think if we solve #1 and I dont have the word "Active" in any other sheet it won't be in the summary. Or is there a better way. Thanks for your help "HH" wrote in message ... Mike, Thanks for your help. I not great at this but I assume I need to insert a sheet labeled "Summary" right click on the tap, and insert the code. If so nothing happened. I'm sure I need to do something else. Side questions: If I insert another sheet to draw different data will that affect the summany sheet code? i.e. will the code you gave try to draw from the new sheet? Hank "Mike H." wrote in message ... Sub Doit() Dim wSheet As Worksheet Dim X As Double Dim DataArray(500, 10) As Variant Dim NewWks As Worksheet Dim TheStr As String Sheets("Summary").Select ActiveWindow.SelectedSheets.Delete For Each wSheet In Worksheets TheStr = wSheet.Name Sheets(TheStr).Select X = X + 1 DataArray(X, 1) = Range("I9") DataArray(X, 2) = Range("C6") 'First Name DataArray(X, 3) = Range("D6") 'Last Name DataArray(X, 4) = Range("N6") ' ID Next wSheet Set NewWks = Worksheets.Add NewWks.Name = "Summary" Sheets("Summary").Select For Y = 1 To X For Z = 1 To 4 Cells(Y + 1, Z + 1).Value = DataArray(Y, Z) Next Next Cells(1, 3).Value = "FName" Cells(1, 4).Value = "LName" Cells(1, 2).Value = "Status" Cells(1, 5).Value = "ID" Cells.Select Cells.EntireColumn.AutoFit Set PrtRng = Range(Cells(1, 1), Cells(5, X + 3)) With ActiveSheet.PageSetup .Zoom = False .PrintArea = PrtRng.Address .PrintTitleRows = "$1:$1" .Orientation = xlPortrait .FitToPagesWide = 1 .FitToPagesTall = 10 End With Cells(2, 1).Select ActiveWindow.FreezePanes = True End Sub "HH" wrote: Although this might be better handled with a true database - like Access, Excel is the only choice I have now. My workbook contains 50+ worksheets labeled 101 to 150. More worksheets will likely be added. Each worksheet contains data on a single individual.. I want Excel to enter data on a summary worksheet (in the same workbook) from each worksheet. Specifically: Cell I9 on every worksheet is the status of he person (active,inactive, etc). Cells C6, D6 and N6 on each sheet contain First names, Last Names, and ID on each person. So if cell I9 status is "active" I want cells B2 - B50(or more if sheets are added) on the new summary sheet to contain the data in C6 of every sheet. C2-C50 would contain data in D6 of each sheet, D2-D50 would contain data in N6 of each sheet. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to list tab/worksheet names in a summary worksheet | Excel Discussion (Misc queries) | |||
Summary worksheet reference to detail worksheet | New Users to Excel | |||
Scan data on worksheet 2 and display a summary on Worksheet 1 | Excel Programming | |||
summary data sheet from worksheet to worksheet | Excel Worksheet Functions | |||
Link worksheet totals to a summary worksheet in the same workbook | Excel Worksheet Functions |