Help with Copy and paste

[menor59]

im using the following VBA

Sub CopyAndPaste()
Dim sh As Worksheet, S As Worksheet
For Each sh In ActiveWorkbook.Worksheets
    If InStr(sh.Name, "Union dues") > 0 Then
        Set S = sh
        Exit For
    End If
Next sh
With Sheets("Blank")
    .Range("AE11:AR11").Copy Destination:=S.Range("G4:T4")
    Application.CutCopyMode = False
End With

End Sub

But its erroring out here

.Range("AE11:AR11").Copy Destination:=S.Range("G4:T4")

Im trying to copy a Range of cells from my work sheet entitled BLANK and paste that range of cells to a work sheet that Contains Union dues with a wildcard at the end...

any thoughts?

[Simon Lloyd]

You haven't SET S so excel doesn't know where to put it!, in your SET you effectively set WORKSHEET to equal WORKSHEET rather than sh.name

[mancubus]

you may try:

[vba]
Sub CopyAndPaste1()
'Worksheet("Union dues") exists
Worksheets("Blank").Range("AE11:AR11").Copy
Worksheets("Union dues").Range("G4:T4").Paste
End Sub

'or

Sub CopyAndPaste1()
'Worksheet("Union dues") exists
Worksheets("Union dues").Range("G4:T4").Value = Worksheets("Blank").Range("AE11:AR11").Value
End Sub
[/vba]



[vba]Sub CopyAndPaste2()
'Worksheet("Union dues") does not exist or not known
On Error Resume Next
Worksheets("Union dues").Range("G4:T4") = Worksheets("Blank").Range("AE11:AR11")
If Err.Number <> 0 Then MsgBox Err.Number & vbCrLf & Err.Description
On Error GoTo 0
End Sub
[/vba]