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

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

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

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

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

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

Make this change

from

If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"), 0))
Then

to

if DestSh.name < sh.Name


This is the only reason I can see the code not working.

"Eduardo" wrote:

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

Hi Ron, thank you for answering, When copying your code I got the error
messages mentioned above, however with Joel modifications I get it working. I
took the example where I copy a range, in some way is working but in this way
Original spreadsheet

Product Region Sales Rep Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Add-On Tools GAF A 10.00 11.00 12 13 14 15 16 17 18 19 20 21


Consolidation sheet

Add-On Tools GAF A 10.00 11.00 12 13 Add-On Tools 15 16 17 18 19 20 21

After 13 it enter the name of the product and repeat it all the way down to
the total rows entered in my range.
For your information The product tabs has different total of rows,
depending on how many regions we sell that product and how many sales people
is responsible for each product. Thank you again for your help


"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

Hi Ron, sorry for bother you again, I figured out (reading your code) why the
name in column H, I am not a VBA expert, I fix everything and is working
execpt for the case where I don't have information in the fills of the range
I would appreciatte if you can help me with this . thank you

"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

I figure out what posted before but I have read all your page and I cannot
find how to exclude the 2 first columns since that ones contain information
for validation data in column B and C

"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

Hi Eduardo

You want to copy from column D till the last column with data
Am I correct



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
I figure out what posted before but I have read all your page and I cannot
find how to exclude the 2 first columns since that ones contain information
for validation data in column B and C

"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

Hi Ron,
Yes correct

"Ron de Bruin" wrote:

Hi Eduardo

You want to copy from column D till the last column with data
Am I correct



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
I figure out what posted before but I have read all your page and I cannot
find how to exclude the 2 first columns since that ones contain information
for validation data in column B and C

"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

One way

'Set the range that you want to copy
Set CopyRng = sh.Range("D" & StartRow & ":IV" & shLast)


You can change the IV column if you want

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
Hi Ron,
Yes correct

"Ron de Bruin" wrote:

Hi Eduardo

You want to copy from column D till the last column with data
Am I correct



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
I figure out what posted before but I have read all your page and I cannot
find how to exclude the 2 first columns since that ones contain information
for validation data in column B and C

"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

Hi Ron, Thank you very much !!, that works perfect
Ron, I have a question, a year ago I needed to change an excel form into an
EDI format, I was told that was not possible, however I broke in part my
problem ask differente questions to the comunity and put together all the
answers, the result was what I was looking form, an order form in an excel
format converted to an EDI format to be uploaded into the company's system.
The comunity has helped me a lot so I'd like to know if there is any place
where I can upload the macro and maybe somebody might use it as well. and
thank you again for your help

"Ron de Bruin" wrote:

One way

'Set the range that you want to copy
Set CopyRng = sh.Range("D" & StartRow & ":IV" & shLast)


You can change the IV column if you want

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
Hi Ron,
Yes correct

"Ron de Bruin" wrote:

Hi Eduardo

You want to copy from column D till the last column with data
Am I correct



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
I figure out what posted before but I have read all your page and I cannot
find how to exclude the 2 first columns since that ones contain information
for validation data in column B and C

"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

Hi Eduardo

If you post it in this newsgroup with a good subject
Anybody can use Google then to find you code example.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
Hi Ron, Thank you very much !!, that works perfect
Ron, I have a question, a year ago I needed to change an excel form into an
EDI format, I was told that was not possible, however I broke in part my
problem ask differente questions to the comunity and put together all the
answers, the result was what I was looking form, an order form in an excel
format converted to an EDI format to be uploaded into the company's system.
The comunity has helped me a lot so I'd like to know if there is any place
where I can upload the macro and maybe somebody might use it as well. and
thank you again for your help

"Ron de Bruin" wrote:

One way

'Set the range that you want to copy
Set CopyRng = sh.Range("D" & StartRow & ":IV" & shLast)


You can change the IV column if you want

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
Hi Ron,
Yes correct

"Ron de Bruin" wrote:

Hi Eduardo

You want to copy from column D till the last column with data
Am I correct



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
I figure out what posted before but I have read all your page and I cannot
find how to exclude the 2 first columns since that ones contain information
for validation data in column B and C

"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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

Thank you for the advice, have a great day

"Ron de Bruin" wrote:

Hi Eduardo

If you post it in this newsgroup with a good subject
Anybody can use Google then to find you code example.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
Hi Ron, Thank you very much !!, that works perfect
Ron, I have a question, a year ago I needed to change an excel form into an
EDI format, I was told that was not possible, however I broke in part my
problem ask differente questions to the comunity and put together all the
answers, the result was what I was looking form, an order form in an excel
format converted to an EDI format to be uploaded into the company's system.
The comunity has helped me a lot so I'd like to know if there is any place
where I can upload the macro and maybe somebody might use it as well. and
thank you again for your help

"Ron de Bruin" wrote:

One way

'Set the range that you want to copy
Set CopyRng = sh.Range("D" & StartRow & ":IV" & shLast)


You can change the IV column if you want

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
Hi Ron,
Yes correct

"Ron de Bruin" wrote:

Hi Eduardo

You want to copy from column D till the last column with data
Am I correct



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
I figure out what posted before but I have read all your page and I cannot
find how to exclude the 2 first columns since that ones contain information
for validation data in column B and C

"Ron de Bruin" wrote:

Read the information good
http://www.rondebruin.nl/copy2.htm


On top of the page you can read

Important:
The macro examples use the LastRow or LastCol function that you can find in the last section of this page.

Copy the macro(s) and function(s) in a standard module of your workbook.
If you have no idea where to paste the code then check out this page.
http://www.rondebruin.nl/code.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Eduardo" wrote in message ...
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