Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Consolidation of Info from Different Tabs
Hello,
I have a workbook with multiple sheets that imports data tables from other sources on a monthly basis. I have created a master sheet that I currently copy and paste the results into to do a pivot table analysis. The problem is that each month when I import the new tables the amount of data (rows) has increased so I have to recopy and paste all data into the master sheet. I don't want to use formulas because I would have to add extra spaces to accomodate for the growing data set. Is there a macro that will find all the data in the other worksheets and copy and paste it into the master? For example, Sheet 1 would have 30 rows, it would find the all the rows with the data and paste it into the master. Sheet 2 for example would have 15 rows, and the macro would identify the 15 rows and paste into the master sheet starting at row 31. Any help would be appreciated. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Consolidation of Info from Different Tabs
If I've understood your question properly, the below should work. It takes
the data from each of the sheets in a workbook (except the first sheet) and pastes it into the first sheet. It assumes that the data on the sheets begins in the upper right (Range A1) and is continuous, which should be reasonable since you mention they come from data tables. Give this a try: Sub CopyDataLoop() Dim wks As Worksheet Dim r As Long For Each wks In ThisWorkbook.Worksheets If wks.Index = 1 Then 'nothing to do here ElseIf wks.Index 1 Then wks.Range("A1").CurrentRegion.Copy _ Destination:=Sheets(1).Cells(r + 1, 1) r = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Co unt End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Consolidation of Info from Different Tabs | Excel Programming | |||
Help w/ formula referring to different tabs & different info in ce | Excel Discussion (Misc queries) | |||
merging info from different tabs | Excel Worksheet Functions | |||
Pulling info from multiple worksheet tabs? | Excel Discussion (Misc queries) | |||
Can I have info on first tab transferred to subsequent tabs? | Excel Worksheet Functions |