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

 

[MARK858]

If You can't use the Inmediate window as MARK858 suggested You can run this code instead

I deliberately specified the Immediate window so it can be copied into the thread (as I requested). A message box doesn't give that option and I want it accurate on what the name is rather than an interpretation of what the message box says.

Btw, what reason could there be why the OP couldn't use the Immediate window?

 

[kalumbair]

Gents,
I'm affraid I'll have to get back to you later, I need to go pick up my car from the shop. Can we revisit this a little late. thanks again for the overwhelming support

 

[urobee]

I deliberately specified the Immediate window so it can be copied into the thread (as I requested). A message box doesn't give that option and I want it accurate on what the name is rather than an interpretation of what the message box says.

Btw, what reason could there be why the OP couldn't use the Immediate window?

I see what was Your plan, it is a much more elegant and expedient way to get the required info than the msgbox.
I've tried Your Inmediate window code, it's working fine to me, so I don't know what was the OP's problem with it, but the OP wrote after Your post:

Mark858,


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

So this is why i suggested the msgbox.

 

[MARK858]

I've tried Your Inmediate window code, it's working fine to me, so I don't know what was the OP's problem whit it, but the OP wrote after Your post:

just ran the code and got no response.

So this is why i suggested the msgbox.

I would have checked first that they had the immediate window open if they aren't getting a response as virtually anything else would throw up an error.
@kalumbair, if you don't see the immediate window it is Ctrl + G when you are in the VB editor.

 

[kalumbair]

so I turned off the spreadsheet and reopened it. here is what it reads in the immediate window.

< &Table2& >
< &Table2& >
< &Table2& >
< &Table2& >
< &Table2& >

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)

 

[MARK858]

Then if there is only one listobject on the sheet your table is called Table2 not HL_COMBINED. Check in Formulas tab - Name Manager to see the list of your table names.

 

[urobee]

So Your table name is not "HL_COMBINED" but Table2.
So the full code in this case:

Private Sub CommandButton1_Click()
    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


Private Sub 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("Table2").Resize Range("$A$1:$D$" & LastRow) ' Change Your table's name and range in this line if it needed!!
End Sub

 

[kalumbair]

ladies and gentlemen, we have lift off!!! thank you for sticking with me and seeing this through

So Your table name is not "HL_COMBINED" but Table2.
So the full code in this case:

Private Sub CommandButton1_Click()
    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


Private Sub 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("Table2").Resize Range("$A$1:$D$" & LastRow) ' Change Your table's name and range in this line if it needed!!
End Sub

 

[urobee]

Glad to help you

 

[kalumbair]

Re: Code Error Run Time erro 13

hello everyone,

I have the below code that i'm trying to run. it's a copy and paste code for last row in table. but I try to execute it I get "error code 1004 application-defined or object-defined error." below is the code, any assistance is greatly appreciated.

Private Sub CommandButton1_Click()
Sheets("HL_1").Range("B" & Rows.Count).End(xlUp).EntireRow.Copy _
Destination:=Sheets("HL_COMBINED").Range("B" & Rows.Count).End(xlUp).Offset(1)
Expandtable
End Sub
Private Sub Expandtable()
Sheets("HL_COMBINED").Select
If ActiveSheet.FilterMode Then ActiveSheet.AutoFilter.ShowAllData
LastRow = ActiveSheet.Range("B1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
ActiveSheet.ListObjects("HL_COMBINED").Resize Range("$B$1:$P$" & LastRow)
End Sub