Why need to use Selection.PasteSpecial vs myRange.PasteSpecial

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

2003/2007

This code works:
Dim SrchAcctRng as Range

SrchAcctRng.Select
SrchAcctRng.Copy
Selection.PasteSpecial xlPasteValues

Next code fails in range class
Dim SrchAcctRng as Range

SrchAcctRng.Copy
SrchAcctRng.PasteSpecial xlPasteValues

Note that SrchAcctRng is a filtered range as a result of:

Set SrchAcctRng = wks.Range(Cells(PrintRow + 1, PrintCol).Address, _
Cells(wks.Cells(Rows.Count, 1).End(xlUp).Row - 1, PrintCol)).SpecialCells(xlCellTypeVisible)


I do not usderstand why I need to use Selection.PasteSpecial xlPasteValues?

TIA EagleOne
 
this works for me:

Sub test()
Dim ws As Worksheet
Dim SrchAcctRng As Range
Set ws = Worksheets("sheet1")
Set SrchAcctRng = ws.Range("A1:C2")
SrchAcctRng.Copy
SrchAcctRng.PasteSpecial xlPasteValues
End Sub
 
sorry, didn't see the filtered range

--


Gary


Gary Keramidas said:
this works for me:

Sub test()
Dim ws As Worksheet
Dim SrchAcctRng As Range
Set ws = Worksheets("sheet1")
Set SrchAcctRng = ws.Range("A1:C2")
SrchAcctRng.Copy
SrchAcctRng.PasteSpecial xlPasteValues
End Sub
Click to expand...
 
Thanks for the thoughts.

Here is what I found:

Using 2007, I had not enabled macros (prompt at open) for the WB in which I was "processing" with
VBA.

When I would get a "macros not enabled" notice, I would reselect Personal.xls in the VBE Explorer
and continue editing as if nothing was wrong.

By not enabling macros in the object w/b, "some" - but not all - of the VBA code would process.

In short, If I enabled macros re: the object w/b in 2007, all VBA works fine. If not, only some VBA
worked.

Thanks EagleOne
 
Back
Top