Offset Code

[bschulze]

I have a piece of code that takes a copied range and pastes it into another worksheet using the offset property. This piece of code is not working and I think that is has something to do with the "Destination".


Dim PasteRng As Range
Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Destination = PasteRng


Any help would be appreciated. Also let me know if there is a better way to write this. Thanks

 

[sweater_vests_rock]

bschulze.

are you copying from another worksheet (ie not summary?) you could try activating the summary sheet before. i always have problems with excel on this. something like

Worksheets("Summary").Activate
PasteRng.PasteSpecial xlPasteValuesAndNumberFormats

ben.

 

[bschulze]

I tried that I am still getting the same problem. The error is "Run-Time error 1004" "PasteSpecial method of range class failed"

I tried changing the code to:
PasteRng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

But that did not work either.

 

[sweater_vests_rock]

hmm. try just .Paste?

sorry for the shoddy help. ben.

 

[bschulze]

I just tried it again.

Dim PasteRng As Range
Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Worksheets("Summary").Activate
ActiveSheet.Paste


I got the same runtime error.

 

[bschulze]

OK I think I have it. There was not a code for the "Copy" function. I had this is the code above but not within the new code. Ok your original code works. Sorry for all the hasle. I forgot the .copy on the back end of the original code.

Thanks for you help

 

[sweater_vests_rock]

no worries. best of luck!
ben.