Hi Joel,
Macro now is working but only bring the information from the first tab
ignoring all the others, any idea

"Joel" wrote:

I'm not sure what isn't working. The only problem I see from your
description is you want only column C and beyond copied rather than the
entire row. Try this change

from
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

to
Set CopyRng = sh.Range(sh.Range("C" & StartRow), _
sh.Range("IV" & shLast))


"Eduardo" wrote:

Hi Joel,
That worked perfectly, however it didn't give me the results I was looking
for, I hope you can help me. I have different tabs for each product with the
information as follow
C D E F ..........
Custom Solutions
2008
100 200
Region Sales Rep Jan Feb
GAF A 100 200

C,D,E&F are the columns in my spreadsheet. Columns A&B are hidden because
contains the list of regions and Sales rep. Custom Solutions is one of my
products then I choose the region, the sales rep and the quantity I think he
is going to sell by month, with a total year. The # on above the month is the
subtotal of the column.
What I need to do is to summarize all the product tabs, (each tab might
contain different rows and regions) in one sheet so I can prepare the
financial statements by region. This spreadsheet is for budget purpose. I
hope I was clear enough and you can help me . Thank you


"Joel" wrote:

Try replacing the two lines

from
Last = LastRow(DestSh)
shLast = LastRow(sh)
to
Last = DestSh.Cells.SpecialCells(xlCellTypeLastCell).Row
shLast = sh.Cells.SpecialCells(xlCellTypeLastCell).Row


"Eduardo" wrote:

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