Activesheet.Paste not working?

[DTM]

I am receiving the following error at the "Activesheet.paste" line i
the following code. I do not understand this because the script worke
before and now it fails almost every time. Is there another way t
paste?:

==============================
Run-time error '1004':

Paste method of Worksheet class failed
==============================

Code as follows
------------------------------------------------------
Public Sub UnprotectSheets(Worksheet As String)
Worksheets(Worksheet).Unprotect password:="password"
End Sub
------------------------------------------------------
Public Sub ProtectSheets(Worksheet As String)
Worksheets(Worksheet).Protect password:="password"
End Sub
------------------------------------------------------
Public Sub Move_Approved()
Dim R As Long
Dim LastRow As Long
Dim PasteRow As Long
Dim Selected As String

Application.EnableEvents = False

'On Error GoTo Exit_Move_Approved

Sheets("Request Purchase").Select
LastRow = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row

If LastRow > 4 Then

For R = 5 To LastRow
If Cells(R, "J").Value <> "" Then
Selected = Selected & R & ":" & R & ","
End If
Next R

Selected = Left(Selected, Len(Selected) - 1)
Range(Selected).Select
Selection.Copy
Sheets("Approved Purchase").Select
PasteRow = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row
1
UnprotectSheets ("Approved Purchase")
Rows(PasteRow & ":" & PasteRow).Select
ActiveSheet.Paste '****FAILS HERE*****
ProtectSheets ("Approved Purchase")
Sheets("Request Purchase").Select
UnprotectSheets ("Request Purchase")
Selection.Delete
ProtectSheets ("Request Purchase")

End If

Exit_Move_Approved:

ProtectSheets ("Request Purchase")
ProtectSheets ("Approved Purchase")
Application.EnableEvents = True

End Su

 

[Michael J. Malinsky]

Try ActiveSheet.PasteSpecial. If you look in the Excel VBA help, you will
see no Paste method.

HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[Medemper]

Your code worked for me as is. I did some playing and couldn't reproduce your error until I played around with the protection and password.

I had tried to remove the password for your macros, manually typed it in to unprotect the Request Purchase sheet and ran the macro - it stopped on the Activesheet.paste line.

Doesn't solve your problem, but might point you in the right direction.
I am receiving the following error at the "Activesheet.paste" line in
the following code. I do not understand this because the script worked
before and now it fails almost every time. Is there another way to
paste?:

==============================
Run-time error '1004':

Paste method of Worksheet class failed
==============================

Code as follows
------------------------------------------------------
Public Sub UnprotectSheets(Worksheet As String)
Worksheets(Worksheet).Unprotect password:="password"
End Sub
------------------------------------------------------
Public Sub ProtectSheets(Worksheet As String)
Worksheets(Worksheet).Protect password:="password"
End Sub
------------------------------------------------------
Public Sub Move_Approved()
Dim R As Long
Dim LastRow As Long
Dim PasteRow As Long
Dim Selected As String

Application.EnableEvents = False

'On Error GoTo Exit_Move_Approved

Sheets("Request Purchase").Select
LastRow = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row

If LastRow > 4 Then

For R = 5 To LastRow
If Cells(R, "J").Value <> "" Then
Selected = Selected & R & ":" & R & ","
End If
Next R

Selected = Left(Selected, Len(Selected) - 1)
Range(Selected).Select
Selection.Copy
Sheets("Approved Purchase").Select
PasteRow = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row +
1
UnprotectSheets ("Approved Purchase")
Rows(PasteRow & ":" & PasteRow).Select
ActiveSheet.Paste '****FAILS HERE*****
ProtectSheets ("Approved Purchase")
Sheets("Request Purchase").Select
UnprotectSheets ("Request Purchase")
Selection.Delete
ProtectSheets ("Request Purchase")

End If

Exit_Move_Approved:

ProtectSheets ("Request Purchase")
ProtectSheets ("Approved Purchase")
Application.EnableEvents = True

End Sub

 

[Frank Kabel]

Hi
try the following code (removed some of your selections, etc.):

Public Sub Move_Approved()
Dim R As Long
Dim LastRow As Long
Dim PasteRow As Long
Dim Selected As String
Dim wks_source As Worksheet
Dim wks_target As Worksheet

Application.EnableEvents = False

'On Error GoTo Exit_Move_Approved

Set wks_source = ActiveWorkbook.Worksheets("Request Purchase")
Set wks_target = ActiveWorkbook.Worksheets("Approved Purchase")
LastRow = wks_source.Cells(Rows.count, "J").End(xlUp).row

If LastRow > 4 Then
For R = 5 To LastRow
If wks_source.Cells(R, "J").Value <> "" Then
Selected = Selected & R & ":" & R & ","
End If
Next R

Selected = Left(Selected, Len(Selected) - 1)
wks_source.Range(Selected).Copy

PasteRow = wks_target.Cells(Rows.count, "J").End(xlUp).row + 1
UnprotectSheets ("Approved Purchase")
wks_target.Paste Destination:=wks_target.Range(PasteRow & ":" &
PasteRow)
ProtectSheets ("Approved Purchase")
wks_source.Select
UnprotectSheets ("Request Purchase")
'Selection.Delete -> do you need this??
ProtectSheets ("Request Purchase")

End If

Exit_Move_Approved:

ProtectSheets ("Request Purchase")
ProtectSheets ("Approved Purchase")
Application.EnableEvents = True

End Sub

 

[Frank Kabel]

Hi Michael
there is a paste method (for the worksheet object) in VBA

 

[DTM]

I found my problem to be with protection so I was able to work aroun
it. It has been several years since I programmed in VB and VBA so I a
a bit rusty. I like your use of things like:

Dim wks_source as Worksheet
Set wks_source = ActiveWorkbook.Worksheets("sheet1")

and then refering to the sheet as an object instead of as a string.
But for some reason I get errors when using this reference like:

=============================
Run-time error '438':

Object doesn't support this property or method
=============================

What am I missing when I execute:

ProtectSheets(wks_source) '*** FAILS HERE ***

Public Sub ProtectSheets(wks_Worksheet As Worksheet)
wks_Worksheet.Protect password:="password"
End Sub


Also I get a:

======================
Compile error

Named argument not found
======================

on the wks_target.Paste Destination=wks_target.Range(PasteRow & ":"
PasteRow)

line.

Thanks
Da

 

[Frank Kabel]

Hi
quite simple
your function 'ProtectSheets' expects a string (and not a worksheet
object). You may either change the parameter type this function is
expecting or you may use (a little bit kludgy)

ProtectSheets(wks_source.name)

 

[DTM]

Frank,

I made a change to ProtectSheets to take a WorkSheet object as a
argument (guess you did not notice it).

ProtectSheets(wks_source) '*** FAILS HERE ***

Public Sub ProtectSheets(wks_Worksheet As Worksheet)
wks_Worksheet.Protect password:="password"
End Sub

Also I get a:

======================
Compile error

Named argument not found
======================

on the:

wks_target.Paste Destination=wks_target.Range(PasteRow & ":"
PasteRow)

line pointing at "Destination". What am I doing wrong.

Thanks
Da

 

[DTM]

I found my compile error but still am puzzled about the WorkShee
objetc