copy past range using variable for start cell address and 2nd variable for end cell address

l_eonandr

New Member
Joined
Feb 18, 2016
Messages
25
Please help
I have searched the web and tried every solution I have found, none worked.
I am trying to copy a range of cells, say T2,AB5 to another sheet. Problem is I don't know where this well appear so have to set a variable.
It may appear several times throughout the data sheet.
each occurrence must be copied to another sheet in succession.

T2,AB5 to sheet2 D6,L9
T33,AB36 to sheet 2 D10,L13
and so on

This is the code I am trying to make work but get a Run-time error '1004': Application-defined or object-defined error

Please tell me what I am doing wrong with this code line??
DestLR is the variable and is dim as Long

Code:
                 Worksheets("Wells").Range(Sheets("Wells").Cells(DestLR, 20), Sheets("Wells").Cells(DestLR + 3, 36)).Copy
                Sheets("Summary").Range(DestLR5, 7).PasteSpecial xlPasteValues
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Code:
Sheets("Summary").[COLOR="#FF0000"][B]Range[/B][/COLOR](DestLR5, 7).PasteSpecial xlPasteValues

looks like it should be

Code:
Sheets("Summary").[COLOR="#FF0000"][B]Cells[/B][/COLOR](DestLR5, 7).PasteSpecial xlPasteValues
 
Upvote 0
Code:
Sheets("Summary").[COLOR=#FF0000][B]Range[/B][/COLOR](DestLR5, 7).PasteSpecial xlPasteValues

looks like it should be

Code:
Sheets("Summary").[COLOR=#FF0000][B]Cells[/B][/COLOR](DestLR5, 7).PasteSpecial xlPasteValues

Thank you so very much!!!!!

It works great, however, I was sure that was what I had started with originally and couldn't get to work so started
searching for solutions and tried several and by the time I posted the question I was using the .Range.

I must have changed something else that effected it in the process. I would never have gone back to the .Cells solution
as I was so frustrated.
Thanks again. but now that that portion of the code is working fine another part which used to work does not. As I tried to find why...
I discovered the button which is on two different sheets but starts the same module works on one but not the other, confusing.

Could it be that I have a statement wrong such that if I use the button on the sheet that is referenced by the line in question it works
but if I use the button on the other sheet it errors out??

here is the line that errors out with "Run-time error '1004' Application-defined or object-defined error";

Code:
                Worksheets("Wells").Range(Cells(DestLR, 20), Cells(DestLR4, 36)).FillRight

again let me say that this works if I am in the sheet that this refers to ie "Wells" and use the button there.

this is puzzling to me because I am using the "Worksheets("Wells") to define where the range is and where the object is???
 
Upvote 0
I forgot to add that in the vb I can click on run continue and the code will complete with everything just as it should be.
 
Upvote 0
Try either

Code:
Worksheets("Wells").Range(Worksheets("Wells").Cells(DestLR, 20), Worksheets("Wells").Cells(DestLR4, 36)).FillRight

or

Code:
With Worksheets("Wells")
  .Range(.Cells(DestLR, 20), .Cells(DestLR4, 36)).FillRight
End With
 
Upvote 0
Thanks a heap. tried the first line and it works!!
My next step now is look over the code and try to find where I can make it run faster. It takes over 5 hours to process 250 pages of data.
I so appreciate your help.
 
Upvote 0
Thanks a heap. tried the first line and it works!!

both codes do exactly the same, the 2nd one is just a different way of laying out the first.

It takes over 5 hours to process 250 pages of data.

What other code do you have?
 
Upvote 0
Wow, just found out how to suppress formula calculation until code completes, now it runs in less than a minute instead of over 5 hours.
Thanks for all the help Mark858
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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