Copy and Paste Problem

[kalumbair]

Hello Everyone,

i'd like to copy the entire last row of a sheet called ("HL_1") and pasted it to another sheet called ("HL_COMBINED"). I should also point out that they are both table formatted. the code i'm using is below, but it's just not working. any assistance is greatly appreciated.

Private Sub CommandButton1_Click()
Dim i, LastRow
LastRow = Sheets(?HL_1?).Cells(Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If Sheets(?HL_1?).Cells(i, ?A?).Value = ?? Then
Sheets(?HL_1?).Cells(i, ?A?).EntireRow.Copy Destination:=Sheets(?HL_COMBINED?).Range(?A? & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

 

[My Aswer Is This]

Why all the ? Marks.

 

[kalumbair]

Lol, something funky must have happened when I paste it. They're not? They're supposed to be quotation marks.

 

[My Aswer Is This]

You said:

i'd like to copy the entire last row of a sheet called ("HL_1") ​

If you want to copy the lastrow why do you think you need a loop with a If Statement looking for nothing?

 

[urobee]

Hy, when You use Cells to refer a cell (not range) try to change the address' of columns from "A" to 1.
Something like this :

[COLOR=#333333][FONT=Helvetica]Private Sub CommandButton1_Click()[/FONT][/COLOR]
[COLOR=#333333][FONT=Helvetica]Dim i, LastRow[/FONT][/COLOR]
[COLOR=#333333][FONT=Helvetica]LastRow = Sheets("HL_1").Cells(Rows.Count).End(xlUp).Row[/FONT][/COLOR]

[COLOR=#333333][FONT=Helvetica]For i = 2 To LastRow[/FONT][/COLOR]
[COLOR=#333333][FONT=Helvetica]If Sheets("HL_1").Cells(i, 1).Value = "" Then[/FONT][/COLOR]
[COLOR=#333333][FONT=Helvetica]Sheets("HL_1").Cells(i, 1).EntireRow.Copy Destination:=Sheets("HL_COMBINED").Range("A" & Rows.Count).End(xlUp).Offset(1)[/FONT][/COLOR]
[COLOR=#333333][FONT=Helvetica]End If[/FONT][/COLOR]
[COLOR=#333333][FONT=Helvetica]Next i[/FONT][/COLOR]
[COLOR=#333333][FONT=Helvetica]End Sub[/FONT][/COLOR]

 

[kalumbair]

You said:

i'd like to copy the entire last row of a sheet called ("HL_1") ​

If you want to copy the lastrow why do you think you need a loop with a If Statement looking for nothing?[/QUOTE

It was a suggestion given to me. But obviously it doesn't work, lol. All I'm trying to do is copy the entire last row of one sheet ("HL_1") and paste it to the last empty Row in another sheet ("HL_COMBINED"). if you can help me simplify this task, I'd be forever grateful.

 

[My Aswer Is This]

Did Urobee's script work for you?

 

[urobee]

I have some time to check your code again, i come up with this code, please check it.
It's working for me.

[COLOR=#333333]Private Sub CommandButton1_Click[/COLOR]()

Sheets("HL_1").Range("A" & Rows.Count).End(xlUp).EntireRow.Copy _
Destination:=Sheets("HL_COMBINED").Range("A" & Rows.Count).End(xlUp).Offset(1)

End Sub

 

[urobee]

Oh and another possible problem:
As You said the two sheets formatted as Table.
If You will use my code You need to expand the table on the target sheet with the new lines.

If You need, use this code:

Private Sub [COLOR=#333333]CommandButton1_Click[/COLOR]()
    Sheets("HL_1").Range("A" & Rows.Count).End(xlUp).EntireRow.Copy _
    Destination:=Sheets("HL_COMBINED").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Expandtable
End Sub

[COLOR=#333333]Private Sub[/COLOR] Expandtable()
    Sheets("HL_COMBINED").Select
    If ActiveSheet.FilterMode Then ActiveSheet.AutoFilter.ShowAllData
    LastRow = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
    ActiveSheet.ListObjects("HL_COMBINED_TABLE").Resize Range("$A$1:$C$" & LastRow) ' Change Your table's name and range in this line if it needed!!
End Sub

 

[kalumbair]

urobee,

thank you for jumping it, really appreciate the support!. the first code you sent worked beautifully, but it didn't paste to the table, instead it just paste the data under it. the second code doesn't work at all and I tried changing the name to what you suggested. pls advise.

v/​

Oh and another possible problem:
As You said the two sheets formatted as Table.
If You will use my code You need to expand the table on the target sheet with the new lines.

If You need, use this code:

Private Sub [COLOR=#333333]CommandButton1_Click[/COLOR]()
    Sheets("HL_1").Range("A" & Rows.Count).End(xlUp).EntireRow.Copy _
    Destination:=Sheets("HL_COMBINED").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Expandtable
End Sub

[COLOR=#333333]Private Sub[/COLOR] Expandtable()
    Sheets("HL_COMBINED").Select
    If ActiveSheet.FilterMode Then ActiveSheet.AutoFilter.ShowAllData
    LastRow = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
    ActiveSheet.ListObjects("HL_COMBINED_TABLE").Resize Range("$A$1:$C$" & LastRow) ' Change Your table's name and range in this line if it needed!!
End Sub