Hi,

In the following function below I am trying to modify Ron de Bruin
function for merge cells from all or some worksheets and am running
into problems with the entry: For Each sh In Sheets(Array("Sheet1",
Sheet2" etc.). I have 24 tabs that can be used for data entry and 2
reserved worksheets for instructions and other macro buttons. You see
that I tried a couple of options that are commented out. The error
message is a runtime error code 9 and hilites the code in yellow
below.

Could someone please assist in educating me on the correct way or other
options available?

Thanks in advance


'Copy a range of each sheet

'This example use the function LastRow


Sub Test1()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"
'For Each sh In ThisWorkbook.Worksheets
'If sh.Name < DestSh.Name Then
'If Left(sh.Name, 4) = "hub" Then

For Each sh In Sheets(Array("hub 1", "hub 2", "hub 3", "hub 4", "hub
6", "hub 7", "hub 8", "hub 9", "hub 10", "hub 11", "hub 12", "hub 13",
"hub 14", "hub 15", "hub 16", "hub 17", "hub 18", "hub 19", "hub 20",
"hub 21"))
Last = LastRow(DestSh)

sh.Range("A1:F295").Copy DestSh.Cells(Last + 2, "A")
'Instead of this line you can use the code below to
copy only the values
'or use the PasteSpecial option to paste the format
also.


'With sh.Range("A1:C5")
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.Range("A1:C5").Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

DestSh.Cells(Last + 2, "C").Value = sh.Name
'This will copy the sheet name in the D column if you
want

'End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub


--
greengrass
------------------------------------------------------------------------
greengrass's Profile: http://www.excelforum.com/member.php...o&userid=23119
View this thread: http://www.excelforum.com/showthread...hreadid=542694