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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top