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
23
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,409
Office Version
365, 2010
Platform
Windows, Mobile
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
 

l_eonandr

New Member
Joined
Feb 18, 2016
Messages
23
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???
 

l_eonandr

New Member
Joined
Feb 18, 2016
Messages
23
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,409
Office Version
365, 2010
Platform
Windows, Mobile
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
 

l_eonandr

New Member
Joined
Feb 18, 2016
Messages
23
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,409
Office Version
365, 2010
Platform
Windows, Mobile
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?
 

l_eonandr

New Member
Joined
Feb 18, 2016
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,952
Messages
5,447,520
Members
405,455
Latest member
nixonphotos

This Week's Hot Topics

Top