Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data from worksheets
I have a workbook with one starting sheet named "master data".
After this sheet, there is a variable amount of sheets containing data. I would like to run the following macro to capture the data i need from each sheet and paste to the master data sheet starting at row 10. each paste should be subsequent to the previous paste. Rows("10:10").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data from worksheets
Hi J.W. Aldridge
Start here http://www.rondebruin.nl/copy2.htm See this example Copy from row 2 till the last row with data -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J.W. Aldridge" wrote in message ... I have a workbook with one starting sheet named "master data". After this sheet, there is a variable amount of sheets containing data. I would like to run the following macro to capture the data i need from each sheet and paste to the master data sheet starting at row 10. each paste should be subsequent to the previous paste. Rows("10:10").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data from worksheets
Ok. But how do i change the range portion Set CopyRng = sh.Range("A1:G1") to what I am needing (below) Rows("10:10").Select Range(Selection, Selection.End(xlDown)).Select |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data from worksheets
'Fill in the start row
StartRow = 2 Change this to 10 Copy the macro and function below in a normal module of your workbook Sub CopyDataWithoutHeaders() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'Fill in the start row StartRow = 10 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If sh.Name < DestSh.Name Then 'Find the last row with data on the DestSh and sh Last = LastRow(DestSh) shLast = LastRow(sh) 'If sh is not empty and if the last row = StartRow copy the CopyRng If shLast 0 And shLast = StartRow Then 'Set the range that you want to copy Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast)) 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If 'This example copies values/formats, if you only want to copy the 'values or want to copy everything look below example 1 on this page CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J.W. Aldridge" wrote in message ... Ok. But how do i change the range portion Set CopyRng = sh.Range("A1:G1") to what I am needing (below) Rows("10:10").Select Range(Selection, Selection.End(xlDown)).Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate data from multiple worksheets into one | Excel Discussion (Misc queries) | |||
I need to consolidate data from 3 different worksheets? | Excel Worksheet Functions | |||
'Consolidate' data from multiple worksheets | Excel Discussion (Misc queries) | |||
How to consolidate data from multiple worksheets. | Excel Worksheet Functions | |||
Consolidate data from several worksheets via pivottable | Excel Worksheet Functions |