Two Copy and Paste issues

s_woodbury

New Member
Joined
Feb 26, 2014
Messages
27
I'm using a macro to copy the entire column A in Sheet 2 and pasting it in COlumn A on Sheet 3. The two issues I'm having are:

1. Copy: I only want to copy down the column until there is a blank cell (each row will have a value in column A until the end of the data is reached). Right now my code is copying the entire column

2. The paste location on Sheet 3 is not static. I need to determine where the data in column A stops so I can select the next cell and paste the data. I'm trying to use Pate Special because when I paste I want the value, not the formula but I get a error when I run the macro.

Here's the code I'm using now:

Sheets("Sheet2").Select
Range("A1").Select
Application.CutCopyMode = False
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet3").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Please help
 
Hi s_woodbury, welcome to the forum

Try this in sheet 2 sheet module.

Regards,
Howard

Code:
Option Explicit

Sub SheetToSheet()

Dim LRow As Long

LRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LRow).Copy
Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
End Sub
 
Upvote 0
L. Howard - I've been meaning to thank you but had problems with my password. This worked great and taught me some new stuff. I appreciate your help!
 
Upvote 0

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