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
194
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,882
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Paste_New_Data()
   Range("G2").Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub
 

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194
How about
VBA Code:
Sub Paste_New_Data()
   Range("G2").Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub
Perfect, Thanks Fluff. Works perfectly.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback.
 

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,507
Members
417,148
Latest member
pe3087te

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