Please help with some simple copy code

[Fatauto]

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

 

[Schodoodles]

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]

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]

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