PDA

View Full Version : Help with Copy and paste



menor59
03-13-2013, 08:21 PM
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
03-13-2013, 09:28 PM
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
03-14-2013, 01:47 AM
you may try:


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




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