Sheet 1 Column A = Sheet 2 Column A Without Copy Cut Pasting

[indi visual]

Sheets(1).Range("A2", Range("A65536").End(xlUp)).Value = _

Sheets(2).Range("A2", Range("A65536").End(xlUp)).Value


I'm looking to import data from another sheet within the same workbook, but without copying and pasting. Any help on what I'm missing here would be phenomenally appreciated

 

[Hui]

If you want to add the last value from Sheet1 Col A to the end of Sheet 2 Col A

try:

Sheets("sheet2").Range("A65536").End(xlUp).Offset(1).Value = Sheets("sheet1").Range("A65536").End(xlUp).Value


If you want to replace the last value in Sheet2 Col A with the last value from Sheet 1 Col A try:

Sheets("sheet2").Range("A65536").End(xlUp).Value = Sheets("sheet1").Range("A65536").End(xlUp).Value

 

[indi visual]

Gotha,


But how exactly do I write this if the sheet names are changing to different various names?

 

[Luke M]

indi,


You need someway of knowing which sheet you are using...are you cycling through all the worksheets? Or, you can use the VB name (in VBE, look in the project explorer window. It'll be the first part the the name. Usually looks something like:


Sheet1 (Name of my sheet)


Where "Sheet1" is the VB name. You can use that in code like:

Sheet1.Range(....)


Note you can change these VB names yourself using the properties window.

 

[indi visual]

Hui | Luke


With Col A in Worksheet(1) empty, and Col A in Worksheet(2) with content... which is what I should have mentioned in the first place (my apologies Hui).


'this places sht2's col A in sht1's col A

Worksheets(1).Columns("A").Value = _

Worksheets(2).Columns("A").Value


However,...


'I was thinking I could select my active content of cell data only

Worksheets(1).Range("A2", Range("A65536").End(xlUp)).Value = _

Worksheets(2).Range("A2", Range("A65536").End(xlUp)).Value


My guess is besides the incorrect syntax, that since Column A in Worksheet(1) is empty, it wouldn't select the entire range of data like on Worksheet(2) because there is no data.


In any case, the only reason I wanted to select my active content only is because I think it's faster (more efficient), and it doesn't active useless blank empty cells when selecting entire columns (so I avoid selecting whole columns like the plague).


Am I right about this?

 

[Luke M]

Worksheets(1).Range("A2", Range("A65536").End(xlUp)).Value = _

Worksheets(2).Range("A2", Range("A65536").End(xlUp)).Value

This won't work because the range sizes will be different and/or as you surmised, having a blank column will just mess things up.


Both lines of code are a single command line, so as far as "efficient", it's simply a matter of how fast the line can be processed. In general, the fewer "." you have in a line of code, the faster it can be processed. So, if you simply want colA of sheet 1 to look like colA of sheet 2, the faster process is:


Worksheets(1).Columns("A").Value = _

Worksheets(2).Columns("A").Value


Just a warning, the "1" and "2" call-outs here simply refer to the sheet order. If someone moves the sheet tabs around, this will get screwed up.

 

[indi visual]

Thanks for the heads up, kinda figured as much. So I'll keep sheet 1 and sheet 2 where they are, and move everything else around after them.


Thanks Hui!


Thanks Luke!


As always.