Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidating sheets - Ron de Bruin can you help me. Thank you
I am working with Excel 2007, What I need to do is to summarize all visible
tabs into one starting in row 5 I tried to use Ron code but is giving me an error message Compile error - Sub or function not defined and highlighted is the sentence as follow Last = LastRow(DestSh). The code I'm using is 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 = 5 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets 'Loop through all worksheets exept the RDBMerge worksheet and the 'Information worksheet, you can ad more sheets to the array if you want. If IsError(Application.Match(sh.Name, _ Array(DestSh.Name, "Information"), 0)) 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidating sheets | Excel Discussion (Misc queries) | |||
Consolidating all sheets | Excel Discussion (Misc queries) | |||
Consolidating data from 4 sheets to one! | Excel Worksheet Functions | |||
Consolidating several sheets into one. | Excel Programming | |||
Consolidating sheets | Excel Worksheet Functions |