With Statement and PasteSpecial

[rob nobel]

I would like to use the PasteSpecial command within a With statement but the
following does not work for me.
With Sheets("Depreciation")
.Unprotect
.Range("J5:J32").Copy
.Range("F5").Select
.PasteSpecial Paste:=xlValues
.Protect
.Range("A1").Select
End With

Can someone advise what's wron with the line....
..PasteSpecial Paste:=xlValues
Thanks,
Rob

 

[Kevin Stecyk]

Rob,

Just guessing (have not tested)....but I don't think
Sheets("Depreciation").PasteSpecial Paste:=xlValues will work.

I think you need a range.

Try the below subrountine.

With Sheets("Depreciation")
.Unprotect
.Range("J5:J32").Copy
.Range("F5").PasteSpecial Paste:=xlValues
.Protect
.Range("A1").Select
End With

Good luck.

Regards,
Kevin

 

[rob nobel]

Hi Kevin,
Thanks for that advice. It works great!
But I have this problem.
If I run your suggested code whilst that worksheet is active, it works fine,
but if I run it when that sheet is not active, I get the following
message....
"Application-defined or object-defined error" when it tries to execute ....
Range ("A1") .Select
I need that line (or something similar) to cancel the highlighted area after
something has been pasted. Is there another way to do that?
I don't know where to go with this. Any suggestion please?
With Sheets("Depreciation")
.Unprotect
.Range("J5:J32").Copy
.Range("F5").PasteSpecial Paste:=xlValues
.Application.CutCopyMode = False
.Range("A1").Select
.Protect
End With
Rob

 

[Dave Peterson]

You can only select a cell on the activesheet.

So you can either
1. get rid of the .range("a1").select
2. select the sheet first
with sheets("depreciation")
.select
'....etc

 

[rob nobel]

Yes Dave, that works OK, but it means I'm back to having the procedure
selecting and showing the sheet, slowing down the procedure and flashing in
between sheets.
Even if I use Application.ScreenUpdating = False, it still goes to sheet2
Is there not a way, using the with statement to avoid this?
AND why doesn't Application.ScreenUpdating = False stop this?
What does Application.ScreenUpdating = False actually do as it seems to
only work in cerain circumstances.
This is what I'm currently running in the sheet1 module but I can't stop it
from showing sheet2 with Application.ScreenUpdating = False and don't know
what else I can do to stop the pasted range from stll being highlighted. I
know it's just a minor issue but it seems there should be any easy answer to
this.
Private Sub test()
With Sheets("Sheet2")
.Select
.Unprotect
.Range("J5:J32").Copy
.Range("F5").PasteSpecial Paste:=xlValues
.Range("A1").Select
.Protect
End With
End Sub

Thanks for all the help,
Rob

 

[J.E. McGimpsey]

First - the macro "goes to" sheet 2 because you Select it.

Application.ScreenUpdating = False will prevent updating the screen
while the macro's running, but once it stops, the update will occur.

Can you use something like:

With Sheets("Sheet2")
.Unprotect
.Range("F5:F32").Value = .Range("J5:J32").Value
.Protect
End With

Yes Dave, that works OK, but it means I'm back to having the procedure
selecting and showing the sheet, slowing down the procedure and flashing in
between sheets.
Even if I use Application.ScreenUpdating = False, it still goes to sheet2
Is there not a way, using the with statement to avoid this?
AND why doesn't Application.ScreenUpdating = False stop this?
What does Application.ScreenUpdating = False actually do as it seems to
only work in cerain circumstances.
This is what I'm currently running in the sheet1 module but I can't stop it
from showing sheet2 with Application.ScreenUpdating = False and don't know
what else I can do to stop the pasted range from stll being highlighted. I
know it's just a minor issue but it seems there should be any easy answer to
this.
Private Sub test()
With Sheets("Sheet2")
.Select
.Unprotect
.Range("J5:J32").Copy
.Range("F5").PasteSpecial Paste:=xlValues
.Range("A1").Select
.Protect
End With
End Sub

Thanks for all the help,
Rob

 

[Dave Peterson]

J.E. gave you a solution that eliminated the ".range("a1").select" line. Did
you really need that?

 

[rob nobel]

Fantastic J.E.
I knew there musta been an easy way for this!!
..Range("F5:F32").Value = .Range("J5:J32").Value is such a good variant to
what I was trying.

Rob

First - the macro "goes to" sheet 2 because you Select it.

Application.ScreenUpdating = False will prevent updating the screen
while the macro's running, but once it stops, the update will occur.

Can you use something like:

With Sheets("Sheet2")
.Unprotect
.Range("F5:F32").Value = .Range("J5:J32").Value
.Protect
End With

 

[rob nobel]

No Dave, I didn't actually want that line but I could not find a way to
deselect the pasted range. What JE has suggested though is a great way for
this situation.
Rob