Offset Code

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
bschulze.

if you are getting a run-time error '91', then change
Code:
Destination = PasteRng
to
Code:
Set Destination = PasteRng
i'm not really sure what you are trying to do, but that will eliminate the error.

ben.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Actually I am not getting an error. It is not pasteing at all. Basically what I am trying to do is append data from another sheet that matches a particular criteria. This is the entire piece of code that relates.


Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Dim PasteRng As Range
Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Destination = PasteRng



It works its way through all the code but never pastes that copied data into the "Summary" sheet.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Actually I am not getting an error. It is not pasteing at all. Basically what I am trying to do is append data from another sheet that matches a particular criteria. This is the entire piece of code that relates.


Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Dim PasteRng As Range
Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Destination = PasteRng



It works its way through all the code but never pastes that copied data into the "Summary" sheet.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289

ADVERTISEMENT

Actually I am not getting an error. It is not pasteing at all. Basically what I am trying to do is append data from another sheet that matches a particular criteria. This is the entire piece of code that relates.


Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Dim PasteRng As Range
Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Destination = PasteRng



It works its way through all the code but never pastes that copied data into the "Summary" sheet.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Actually I am not getting an error. It is not pasteing at all. Basically what I am trying to do is append data from another sheet that matches a particular criteria. This is the entire piece of code that relates.


Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Dim PasteRng As Range
Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Destination = PasteRng



It works its way through all the code but never pastes that copied data into the "Summary" sheet.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289

ADVERTISEMENT

Actually I am not getting an error. It is not pasteing at all. Basically what I am trying to do is append data from another sheet that matches a particular criteria. This is the entire piece of code that relates.


Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Dim PasteRng As Range
Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Destination = PasteRng



It works its way through all the code but never pastes that copied data into the "Summary" sheet.
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
bschulze.

ohhh, much clearer now (esp. after 4 or 5 posts :))

i think what you have is close, but let's reorder it a bit and clean up what the macro recorder has written. not being able to test this, i would guess that the code you want is as follows:

Code:
Sub Test()

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

Rows("1:1").AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
Range(Range("A2:B2"), Range("A2:B2").End(xlDown)).Copy
PasteRng.PasteSpecial xlPasteValuesAndNumberFormats

End Sub

hope this is more helpful.
ben.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
It looks like it is going to work this way. I am getting a runtime error on the last piece of code "PasteRng.PasteSpecial xlPasteValuesAndNumberFormats"

Before it errors out it selected the proper cell so I think that it is going to work. I played around with the last piece of code but could not get it to work.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,013
Members
412,304
Latest member
citrus
Top