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]

If both sheets have Tables we are working with I always prefer to deal with table names

So are you wanting to copy the last row in Table1 and add it after the lastrow in Table2

 

[kalumbair]

I'm afraid the rocket is still grounded. I tried using table name and got Run-time error '9': subscript out of range. it's this last bit of code that seems to be the problem:
ActiveSheet.ListObjects("HL_COMBINED").Resize Range("$A$1:$C$" & LastRow)

 

[urobee]

Please send your table’s name and range from the hl_combined sheet.

 

[kalumbair]

urobee,

the two sheets are ("HL_1") and ("HL_COMBINED"). The tables are named the same. hope that helps and thanks again.

 

[urobee]

Ok, I see Your new message and You changed the name of the table. it's good!

I colored the table's name with green it's ok if it's the actual name of the table is HL_COMBINED.
ActiveSheet.ListObjects("HL_COMBINED").Resize Range("$A$1:$C$" & LastRow)

The next step:
You need to change the first cell and the last column of the table.
(colored with orange (or something )

If your table (with/or without header) on HL_COMBINED sheet is start from A1 cell than it is fine, don't change it. If the table's first cell is somewhere else than change the $A$1 to it.
If the first cell is is A1 than You will need to change only the $C$ part to the actual last column of the table.
For example if the first cell is B2 and the last column is AX than:
ActiveSheet.ListObjects("HL_COMBINED").Resize Range("$B$2:$AX$" & LastRow)

 

[kalumbair]

man, this nut doesn't seem to want to crack. I did what you suggested and still no dice. so to make sure I understand, my table has headers and starts with A1 and ends in D so I made the following change. ActiveSheet.ListObjects("HL_COMBINED

").Resize Range("​

$A$1:$D$

" & LastRow). is that what you meant?

​

Ok, I see Your new message and You changed the name of the table. it's good!

I colored the table's name with green it's ok if it's the actual name of the table is HL_COMBINED.
ActiveSheet.ListObjects("HL_COMBINED").Resize Range("$A$1:$C$" & LastRow)

The next step:
You need to change the first cell and the last column of the table.
(colored with orange (or something )

If your table (with/or without header) on HL_COMBINED sheet is start from A1 cell than it is fine, don't change it. If the table's first cell is somewhere else than change the $A$1 to it.
If the first cell is is A1 than You will need to change only the $C$ part to the actual last column of the table.
For example if the first cell is B2 and the last column is AX than:
ActiveSheet.ListObjects("HL_COMBINED").Resize Range("$B$2:$AX$" & LastRow)

 

[MARK858]

@kalumbair, if you run the code below what result do you get it the immediate window? (by what result I mean literally copy and paste it in the thread).

Sub testit()
Debug.Print "< &"; Sheets("HL_COMBINED").ListObjects(1).Name; "& >"
End Sub

 

[urobee]

Thanks it's a great tip!
I only get the Run-time error '9' error when the table name was not the given "HL_COMBINED".

 

[kalumbair]

Mark858,

thank for jumping in, appreciate all the help. just ran the code and got no response.

 

[urobee]

Yes, Your changes looks good, so the error will be somewhere else.

If You can't use the Inmediate window as MARK858 suggested You can run this code instead (based on MARK858 code, thanks again!):
Just copy and run it from the VBA editor. You will be get a message with the name of the table we (You) need. Copy that name from letter-to-letter.

Sub testit()
MsgBox Sheets("HL_COMBINED").ListObjects(1).Name
End Sub

BTW the Inmediate window suggestion is a faster way to get info

BUT... If You don't want to fight with the names of the tables and the HL_COMBINED sheet has only one table you can change this part:

ActiveSheet.ListObjects("HL_COMBINED").Resize Range("$A$1:$D$" & LastRow)

to this:

ActiveSheet.ListObjects(1).Resize Range("$A$1:$D$" & LastRow)