Merge different Work books
 

I have an Macro Which Merges Different Sheets in to a Master Sheet.. But i
have a Problem with the Macro that its not ,Merging all the Sheets and
Complete Data Range.. So if any One Could help me.. it would be Greatly
Appreciated....
My Macro as Follows:

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

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"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name Then


'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look at the example below
this macro
CopyRng.Copy DestSh.Cells(Last + 1, "A")

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

Merge different Work books
 

You not use all the code from my example
http://www.rondebruin.nl/copy2.htm


For example I miss this

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

Why do you delete code from a working example ???


--

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




"Kumar" wrote in message ...
I have an Macro Which Merges Different Sheets in to a Master Sheet.. But i
have a Problem with the Macro that its not ,Merging all the Sheets and
Complete Data Range.. So if any One Could help me.. it would be Greatly
Appreciated....
My Macro as Follows:

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

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"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name Then


'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look at the example below
this macro
CopyRng.Copy DestSh.Cells(Last + 1, "A")

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