Thanks for that... it doesnt seam to be working...
Ive set it up so that it only does sheets starting with cats...
and from each sheet i need the area from A1 to R17.... have I got that
right.......
I think its not working because of the Last Row ... im not sure why
though...
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 = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If Left(sh.Name, 4) = "cats" Then
Last = LastRow(DestSh)
sh.Range("A1:R17").Copy DestSh.Cells(Last + 1, "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 + 1, "D").Value = sh.Name
'This will copy the sheet name in the D column if you want
End If
Next
Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub
its working well....