LRow of table

[Takes2ToTango]

Hi all,

I am running into an issue with populating the lrow of a table with copied data. I have pasted the code below. I am trying to get it to copy the data and then with the sheet 'DateQuery' I want it to paste into the lrow of the table. However, I am either getting data overridden or missing the table completely. I have also tried adding '+ 1' onto the end of the .Row.

  With ws2
        lRow2 = Cells(Rows.Count, 1).End(xlUp).Row
    'Loop from bottom to top deleting according to value in 1st column
        For iCntr2 = lRow2 To 2 Step -1
        
           If Cells(iCntr2, 6) = "QUERY" Then
            Rows(iCntr2).Copy
                With Sheets("DateQuery")
                 lRow3 = Sheets("DateQuery").Range("A" & Rows.Count).End(xlUp).Row  'Tried + 1 on the end of here but causes it to miss the table
                .Range("A" & lRow3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                End With
            Rows(iCntr2).Delete
            End If
        Next
      End With

 

[Legacy 143009]

It is very nasty to nest two Withs together, moreover, with the same type...

Also you have to add a leading . if you want to refer to ws2. I don't know your data astructure but you need something like this:

    With ws2
    lRow2 = .Cells(Rows.Count, 1).End(xlUp).Row
    For iCntr2 = lRow2 To 2 Step -1
      If .Cells(iCntr2, 6) = "QUERY" Then
        .Rows(iCntr2).Copy
        Sheets("DateQuery").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        .Rows(iCntr2).Delete
      End If
    Next
    End With

 

[Takes2ToTango]

Thanks for your reply. I've used your code as you have said. It is still missing the first line of the table (completely missing the table as a whole and not becoming part of it). Do I need the offset part in there?

 

[Legacy 143009]

Do I need the offset part in there?

Yes it will allow you to continue from the next available row.

I have no idea why are you still missing the table. Does your data start from column A?

 

[Takes2ToTango]

This is my table here. It starts in range "A1" and I have placed a dummy line in there as tests for the lrow error. Could this be throwing it off perhaps?

 

[Legacy 143009]

No, I mean your table in ws2?

 

[Takes2ToTango]

Ah my apologies I read it wrong! Yes the table in ws2 is identical to the one above (without the dummy line) starts in range "A1"

 

[Legacy 143009]

The code works perfectly fine for me. Please find the sample file

 

[Takes2ToTango]

The code works! I think it could be the table isn't auto-expanding to capture the new data.

 

[Legacy 143009]

I am glad it did work! Thanks for the follow-up