Copy a cell data and past to another worksheet to the last available cell in a column

Wes

Board Regular
Joined
Jan 30, 2004
Messages
195
Hi All,

Im just building a simple function but have been away for sometime on other projects. Could I get a hand with this one. Im copying from an activeSheet ("G") to a destination sheet ("Sheet 3") into the first column. I'd like to continue adding onto the bottom of the list. What has happen is I cant keep the xlDown into Column 1. It jumps to Column 4 and Ive changed it to paste into Column 1 but it gives an error. Also It pasts into the 3 cell of the column and doest continue to add to the bottom of the list. Im open to Columns or tables to paste into.

Thanks in advance all.

Sub Paste_New_Data()
'Copy and Paste between worksheets

ActiveSheet.Range("G2").Copy
Worksheets("Sheet3").Columns(1).Cells(1, 4).End(xlDown).Offset(1).Select
ActiveSheet.Paste

End Sub
••••ˇˇˇˇ
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
That is because
Cells(1,4) is in the 4th column (it is Cells(row, column)).

Try changing this line:
VBA Code:
Worksheets("Sheet3").Columns(1).Cells(1, 4).End(xlDown).Offset(1).Select
to
VBA Code:
Worksheets("Sheet3").Cells(1,1).End(xlDown).Offset(1).Select
 
Upvote 0
How about
VBA Code:
Sub Paste_New_Data()
   Range("G2").Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
That is because
Cells(1,4) is in the 4th column (it is Cells(row, column)).

Try changing this line:
VBA Code:
Worksheets("Sheet3").Columns(1).Cells(1, 4).End(xlDown).Offset(1).Select
to
VBA Code:
Worksheets("Sheet3").Cells(1,1).End(xlDown).Offset(1).Select
Thanks Joe4. I did chug the 4 to a one but for mose reason it was crashing. Got it now. Cheers
 
Upvote 0
Thanks Joe4. I did chug the 4 to a one but for mose reason it was crashing. Got it now. Cheers
Having both .Columns and .Cells in the same line is problematic.
It cannot tell whether you want to select a column or a single cell.
You only use one or the other, never both at the same time.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top