Menu
I'm trying to write a macro that copies the content of column 1 from sheet 1 to column 2 on sheet 2. This is how the module looks like but, when I run it, I get
Run time error 9, Subscript out of range.
Sub OneCell()
Sheets("Sheet1").Select
'select column 1 A1'
Range("A1:A3").Select
Selection.Copy
Range("B1:B3").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
End Sub
684
Type a comma, and then click on the sheet that you want to pull data from. Click and drag over the two columns that hold your data. Type another comma, and then type the number of the column that contains the data you want to pull across. In this case, it's the second column, so we would type 2.
To copy a cell or a range of cells to another worksheet you need to use the VBA's “Copy” method. In this method, you need to define the range or the cell using the range object that you wish to copy and then define another worksheet along with the range where you want to paste it.
The following works fine for me in Excel 2007. It is simple, and performs a full copy (retains all formatting, etc.):
Sheets("Sheet1").Columns(1).Copy Destination:=Sheets("Sheet2").Columns(2)
"Columns" returns a Range object, and so this is utilizing the "Range.Copy" method. "Destination" is an option to this method - if not provided the default is to copy to the paste buffer. But when provided, it is an easy way to copy.
As when manually copying items in Excel, the size and geometry of the destination must support the range being copied.
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
