Please help with some simple copy code

Fatauto

New Member
Joined
Jan 25, 2005
Messages
6
I'm looking to copy some data form one sheet to another. The code I'm using is shown below, and works when I'm copy from the the "Table" sheet to the "Table" sheet but errors when I copy from the "Data" sheet to the "Table" sheet. The numbers in the cell references are actualy going to be replaced with variables as the range changes due to user inputs.

Works:

Sheets("Table").Range(Cells(2, 3), Cells(2, 4)).Copy _
Destination:=Sheets("Table").Range("B21")

Fails:

Sheets("Data").Range(Cells(2, 3), Cells(2, 4)).Copy _
Destination:=Sheets("Table").Range("B21")

I'm new to VBA so parden the weak question

Thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Schodoodles

New Member
Joined
Jan 25, 2005
Messages
17
Try changing it so that the range is shown more like A1:A5

so;
Sheets("Data").Range(A2:A3), Range(C4:C7).Copy
Destination:=Sheets("Table").Range("B21")

It's a possible - if this doesnt work I'd suggest a macro.
1)Simply click record
2)Use SHIFT + ALT and click the area you want to copy
3)Go to the menu and select copy
4)Go to place you want to copy to and either click and use menu or RHMB and select paste.
5)Click stop

After this go into the code (ALT+F11) and go to modules using left hand bar, double click the recorded macro and check out the code to see how it is done. Probably a safer method than the first too!
 

Fatauto

New Member
Joined
Jan 25, 2005
Messages
6
The problem is that I want to use variables in place of the cell numbers. As in I want to copy Range(cells(beginrow, begincolumn) , cells(endrow, endcolumn) and the values of the 4 variables changes based on a bunch of diffferent comands and lists that run before this line of code does. So I can't just say "A3:A5" becasue one time it may be "A3:A5" but the next it may be "G6:G23" That why I am trying to leave it just as variables, it makes the start and stop points easy to define
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hi Fatauto, welcome to the board.
If you want to actually copy (as in retain formatting, formulas, etc.) then you should be able to use this with variables.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test1()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> Range, x <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> i = Sheets("Data").Range(Cells(2, 3), Cells(2, 4))
<SPAN style="color:#00007F">Set</SPAN> x = Sheets("Table").Range("B21")
i.Copy x
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


If, however, you just want to transport the values, and not the formatting, formulas, etc, you can do it without copying at all:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test2()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> Range, x <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> i = Sheets("Data").Range(Cells(2, 3), Cells(2, 4))
<SPAN style="color:#00007F">Set</SPAN> x = Sheets("Table").Range("B21:C21")
x.Value = i.Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


In either case, you can define the variables however you like.
Hope it helps,
Dan
 

Forum statistics

Threads
1,147,846
Messages
5,743,518
Members
423,801
Latest member
paulj4177

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
Top