Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking multiple sheets to one master
Guys - Im really struggling with this on..
I am receiving 7 submission from different areas which I copy into different tabs in a central workbook that I manage, what im looking to do is have a consolidated view so a sheet that will pull all of the data from each individual tab into one full list. The problem I have is that the individual submission are constantly growing in size so I am unable to reference to particular cells without leaving big gaps in the summary tab |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking multiple sheets to one master
Hi
Create yourself a new sheet called "All Data" and set up a header row to match your other sheets. Then, use the following code to create the summary. Run the code whenever you have new data in any of your sheets, as it will first clear the Summary sheet, then rebuild the data. Sub combinesheets() Dim sht As Worksheet, SumSht As Worksheet Dim NewRow As Long, LRow as Long Application.ScreenUpdating = False NewRow = 2 Set SumSht = Sheets("All Data") SumSht.Range("2:65536").Delete For Each sht In ThisWorkbook.Sheets If sht.Name < "All Data" Then ' this is set to copy columns A to K from each sheet ' and to place the source sheet name in column L ' Amend column letters to suit your requirement LRow = sht.Range("A" & Rows.Count).End(xlUp).Row sht.Range("A2:K" & LRow).Copy SumSht.Range("A" & NewRow) SumSht.Range("L" & NewRow & ":L" & NewRow + LRow - 2) = sht.Name NewRow = NewRow + LRow - 1 End If Next sht With SumSht Columns("A:L").EntireColumn.AutoFit Range("A2").Select ActiveWindow.FreezePanes = True End With End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Alt+F8 to bring up Macros Highlight the macro name Run -- Regards Roger Govier "Roachy" wrote in message ... Guys - Im really struggling with this on.. I am receiving 7 submission from different areas which I copy into different tabs in a central workbook that I manage, what im looking to do is have a consolidated view so a sheet that will pull all of the data from each individual tab into one full list. The problem I have is that the individual submission are constantly growing in size so I am unable to reference to particular cells without leaving big gaps in the summary tab |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking multiple sheets to one master
Roger this is fantastic and works a treat, the only thing is it seems to be
copying the data across twice, any ideas??? "Roger Govier" wrote: Hi Create yourself a new sheet called "All Data" and set up a header row to match your other sheets. Then, use the following code to create the summary. Run the code whenever you have new data in any of your sheets, as it will first clear the Summary sheet, then rebuild the data. Sub combinesheets() Dim sht As Worksheet, SumSht As Worksheet Dim NewRow As Long, LRow as Long Application.ScreenUpdating = False NewRow = 2 Set SumSht = Sheets("All Data") SumSht.Range("2:65536").Delete For Each sht In ThisWorkbook.Sheets If sht.Name < "All Data" Then ' this is set to copy columns A to K from each sheet ' and to place the source sheet name in column L ' Amend column letters to suit your requirement LRow = sht.Range("A" & Rows.Count).End(xlUp).Row sht.Range("A2:K" & LRow).Copy SumSht.Range("A" & NewRow) SumSht.Range("L" & NewRow & ":L" & NewRow + LRow - 2) = sht.Name NewRow = NewRow + LRow - 1 End If Next sht With SumSht Columns("A:L").EntireColumn.AutoFit Range("A2").Select ActiveWindow.FreezePanes = True End With End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Alt+F8 to bring up Macros Highlight the macro name Run -- Regards Roger Govier "Roachy" wrote in message ... Guys - Im really struggling with this on.. I am receiving 7 submission from different areas which I copy into different tabs in a central workbook that I manage, what im looking to do is have a consolidated view so a sheet that will pull all of the data from each individual tab into one full list. The problem I have is that the individual submission are constantly growing in size so I am unable to reference to particular cells without leaving big gaps in the summary tab |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking multiple sheets to one master
Change "Master" to the name of your summary sheet.
However, maybe you could only have the summary sheet and use filters to work with the data. Sub copyshtstomaster()'SAS copies each ws row2 down Dim sumsht As String Dim ws As Worksheet Dim lr, slr As Long sumsht = "Master" With Sheets(sumsht) ..UsedRange.Rows.Delete For Each ws In Worksheets If ws.Name < sumsht Then lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 slr = ws.Cells.Find("*", _ Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row ws.Rows(2).Resize(slr - 1).Copy .Cells(lr, "a") End If Next ws End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Roachy" wrote in message ... Guys - Im really struggling with this on.. I am receiving 7 submission from different areas which I copy into different tabs in a central workbook that I manage, what im looking to do is have a consolidated view so a sheet that will pull all of the data from each individual tab into one full list. The problem I have is that the individual submission are constantly growing in size so I am unable to reference to particular cells without leaving big gaps in the summary tab |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking multiple sheets to one master
HI
i can't see why it should. The routine is looping through each sheet in the Worksheets collection, and, if the sheet name is not equal to All Data, then copying the values. Do you have sheets duplicated and hidden? You can't be inadvertently running the macro twice, as the first thing it does is to delete the entries other than the header from All Data. -- Regards Roger Govier "Roachy" wrote in message ... Roger this is fantastic and works a treat, the only thing is it seems to be copying the data across twice, any ideas??? "Roger Govier" wrote: Hi Create yourself a new sheet called "All Data" and set up a header row to match your other sheets. Then, use the following code to create the summary. Run the code whenever you have new data in any of your sheets, as it will first clear the Summary sheet, then rebuild the data. Sub combinesheets() Dim sht As Worksheet, SumSht As Worksheet Dim NewRow As Long, LRow as Long Application.ScreenUpdating = False NewRow = 2 Set SumSht = Sheets("All Data") SumSht.Range("2:65536").Delete For Each sht In ThisWorkbook.Sheets If sht.Name < "All Data" Then ' this is set to copy columns A to K from each sheet ' and to place the source sheet name in column L ' Amend column letters to suit your requirement LRow = sht.Range("A" & Rows.Count).End(xlUp).Row sht.Range("A2:K" & LRow).Copy SumSht.Range("A" & NewRow) SumSht.Range("L" & NewRow & ":L" & NewRow + LRow - 2) = sht.Name NewRow = NewRow + LRow - 1 End If Next sht With SumSht Columns("A:L").EntireColumn.AutoFit Range("A2").Select ActiveWindow.FreezePanes = True End With End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Alt+F8 to bring up Macros Highlight the macro name Run -- Regards Roger Govier "Roachy" wrote in message ... Guys - Im really struggling with this on.. I am receiving 7 submission from different areas which I copy into different tabs in a central workbook that I manage, what im looking to do is have a consolidated view so a sheet that will pull all of the data from each individual tab into one full list. The problem I have is that the individual submission are constantly growing in size so I am unable to reference to particular cells without leaving big gaps in the summary tab |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking multiple sheets to one master
I would like to copy only some of the columns for example Column A & K
how would you change the code to do this? Also i would like to only copy a row if in column AA is the word "Data" Thanks Jack "Roger Govier" wrote: Hi Create yourself a new sheet called "All Data" and set up a header row to match your other sheets. Then, use the following code to create the summary. Run the code whenever you have new data in any of your sheets, as it will first clear the Summary sheet, then rebuild the data. Sub combinesheets() Dim sht As Worksheet, SumSht As Worksheet Dim NewRow As Long, LRow as Long Application.ScreenUpdating = False NewRow = 2 Set SumSht = Sheets("All Data") SumSht.Range("2:65536").Delete For Each sht In ThisWorkbook.Sheets If sht.Name < "All Data" Then ' this is set to copy columns A to K from each sheet ' and to place the source sheet name in column L ' Amend column letters to suit your requirement LRow = sht.Range("A" & Rows.Count).End(xlUp).Row sht.Range("A2:K" & LRow).Copy SumSht.Range("A" & NewRow) SumSht.Range("L" & NewRow & ":L" & NewRow + LRow - 2) = sht.Name NewRow = NewRow + LRow - 1 End If Next sht With SumSht Columns("A:L").EntireColumn.AutoFit Range("A2").Select ActiveWindow.FreezePanes = True End With End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Alt+F8 to bring up Macros Highlight the macro name Run -- Regards Roger Govier "Roachy" wrote in message ... Guys - Im really struggling with this on.. I am receiving 7 submission from different areas which I copy into different tabs in a central workbook that I manage, what im looking to do is have a consolidated view so a sheet that will pull all of the data from each individual tab into one full list. The problem I have is that the individual submission are constantly growing in size so I am unable to reference to particular cells without leaving big gaps in the summary tab |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking multiple worksheets and master completes next available li | Excel Worksheet Functions | |||
Pulling information from master sheet to multiple sheets | New Users to Excel | |||
Master worksheet linking to multiple worksheets and using filter | Excel Discussion (Misc queries) | |||
Multiple text sheets into a master worksheet as you enter data | Excel Worksheet Functions | |||
linking multiple excel files to a master | Excel Discussion (Misc queries) |