Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate data from multiple worksheets into one
I have a workbook with multiple worksheets representing different
departments. The colum headings are exactly the same for each department. I want to create a main worksheet that includes all data from these different worksheets, however, this is a "live" workbook that will have new data entered on a consistant basis. Is it possible to have a worksheet that is autopopulated with the new data as it is entered to the other worksheets?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate data from multiple worksheets into one
Barton,
Bite the bullet, and get rid of all the extra sheets: use just one sheet, with another column denoting the department (use the sheet names from the old workbook). Then enter the data with that one extra field, and use filters and/or pivot tables to display/ use your data. The macro below will create the database for you from your existing workbook. It assumes that all your sheets start in cell A1, all have the same headers (in row 1), and the same column arrangement. After running the macro, delete the original sheets and save the file under a different name. HTH, Bernie MS Excel MVP Sub ConsolidateSheetsIntoDataBase() With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With Dim i As Integer Dim myDB As Worksheet Set myDB = Worksheets.Add(Befo=Worksheets(1)) myDB.Name = "DataBase Sheet" Worksheets(2).Cells.Copy Worksheets(1).Cells With Worksheets(1) .Cells(1, 1).EntireColumn.Insert .Cells(1, 1).Value = "Department" Intersect(.Range("A2:A" & .Rows.Count), .UsedRange).Value = Worksheets(2).Name End With For i = 3 To Worksheets.Count With Worksheets(1) myRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Worksheets(i).Cells(1, 1).CurrentRegion.Copy Worksheets(1).Cells(myRow, 2) Intersect(.Range("A" & myRow & ":A" & .Rows.Count), .UsedRange).Value = Worksheets(i).Name End With Next i With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "bartonHR" wrote in message ... I have a workbook with multiple worksheets representing different departments. The colum headings are exactly the same for each department. I want to create a main worksheet that includes all data from these different worksheets, however, this is a "live" workbook that will have new data entered on a consistant basis. Is it possible to have a worksheet that is autopopulated with the new data as it is entered to the other worksheets?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate data from multiple worksheets into one
Oops, I forgot to take the extra headers out... use this version.
HTH, Bernie MS Excel MVP Sub ConsolidateSheetsIntoDataBase() With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With Dim i As Integer Dim myDB As Worksheet Set myDB = Worksheets.Add(Befo=Worksheets(1)) myDB.Name = "DataBase Sheet" Worksheets(2).Cells.Copy Worksheets(1).Cells With Worksheets(1) .Cells(1, 1).EntireColumn.Insert .Cells(1, 1).Value = "Department" Intersect(.Range("A2:A" & .Rows.Count), _ .UsedRange).Value = Worksheets(2).Name End With For i = 3 To Worksheets.Count With Worksheets(1) myRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Worksheets(i).Cells(1, 1).CurrentRegion.Offset(1) _ .Copy Worksheets(1).Cells(myRow, 2) Intersect(.Range("A" & myRow & ":A" & .Rows.Count), _ .UsedRange).Value = Worksheets(i).Name End With Next i With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "bartonHR" wrote in message ... I have a workbook with multiple worksheets representing different departments. The colum headings are exactly the same for each department. I want to create a main worksheet that includes all data from these different worksheets, however, this is a "live" workbook that will have new data entered on a consistant basis. Is it possible to have a worksheet that is autopopulated with the new data as it is entered to the other worksheets?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to consolidate multiple worksheets into one. | Excel Discussion (Misc queries) | |||
Consolidate multiple worksheets in a workbook into one | Excel Discussion (Misc queries) | |||
'Consolidate' data from multiple worksheets | Excel Discussion (Misc queries) | |||
How to consolidate data from multiple worksheets. | Excel Worksheet Functions | |||
Consolidate list from multiple worksheets | Excel Worksheet Functions |