Last row starting from row A

It's starting in A2 because you have Offset(1)

Generally, you will need to do that to not overwrite the value in a table's last row.

You could check to see if you are at the top of the sheet, or if the value is blank,

IF Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).row =1 THEN 'This tells you that you have nothing in the A column below the first row - the first row may or may not be blank.  
    Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).PasteSpecial xlPasteAll
ELSE
    Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteAll 'You did not make it to the first row - so there is data in the A column.  The offset(1) prevents over-writing the last row.
END IF

For the pasteSpecial versus paste, just try it both ways and see what happens. Generally, you want to clean your code of this kind of statement anyway.

If you are trying to copy over some values, there are better ways to do that.

Just as I suspected... there is no need for the .copy / .pasteSpecial funny business.

Here's what I suggest instead.

For i = 1 to 12

	IF sheets("Sheet 1").cells(i,1).value = "Copy Me" then

		IF sheets("Sheet 2").cells(1,1) ="" THEN
			sheets("Sheet 2").cells(1,1).value = sheets("Sheet 1").cells(i,1).value
		ELSE
			sheets("Sheet 2").cells(rows.Count,1).end(xlUp).offset(1,0).value = sheets("Sheet 1").cells(i,1).value
		END IF	

	END IF

NEXT I

Barring any typos (I just typed that into the browser) that should do it. You can just equate the values and avoid the copy/paste stuff. It's a really good habit to get away from, it's something we all do at one point because the Macro recorder does it....

Hi ammartino,

It looks like you are starting to learn to program in VBA and Excel. Each of us develops a style of programming that we understand.

One of the first problems is to extract a cell using VBA. Here are a few ways to get the stuff out of B7.
Range("B7")
Cells(7, "B")
Read my favorite author's answer at: http://spreadsheetpage.com/index.php...your_vba_code/

I rarely use any "Offset" methods in my programming as it needs to know what cell the offset is from and this is one more level of confusion for me.

The next style concern is to use a For Next construct or a For Each construct.

I prefer the older For Next construct as I like to know each row and column I'm working with.

I also don't like to use variable names of j,k,l as a more meaningful name makes code much more readable.

See which of these two subs make more sense. What kind of coder do you want to be?

Sub CopyMeSht1to2()
    Dim LastRowA As Double
    Dim RowCtr As Double
    
    LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
    
    For RowCtr = 1 To LastRowA
        If Cells(RowCtr, "A") = "Copy Me" Then
            Sheets("Sheet2").Cells(RowCtr, "A") = Cells(RowCtr, "A")
        End If
    Next RowCtr
    
End Sub

Sub Test()
Dim lr, i
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If Range("A" & i).Text = "Copy Me" Then
Sheets(2).Range("a" & i) = "Copy Me"
End If
Next
End Sub

Look at http://www.excelfunctions.net/VBA-Co...sentation.html for a start to indentation and line breaks.

OK - I missed that part of the problem. No problem reading my code...
How about this then.

Sub CopyMeSht1to2()
    Dim LastRowA As Double
    Dim LastRowSht2 As Double
    Dim RowCtr As Double
    
    LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
    
    For RowCtr = 1 To LastRowA
        If Cells(RowCtr, "A") = "Copy Me" Then
            LastRowSht2 = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
            Sheets("Sheet2").Cells(LastRowSht2 + 1, "A") = Cells(RowCtr, "A")
        End If
    Next RowCtr
    
End Sub