Two Copy and Paste issues

[s_woodbury]

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

 

[L. Howard]

Hi s_woodbury, welcome to the forum

Try this in sheet 2 sheet module.

Regards,
Howard

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

 

[s_woodbury]

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!

 

[L. Howard]

You're welcome and thanks for the feedback.

Regards,
Howard