VBA: Copy last row from table cause error

Question

I have two tables. First is a list of rows where user needs to put comment in proper column. Second is database.

If user inserts anything to comment column then that row is copied to another sheet (database).

My code works good until user inserts comment in last row user table. Then he gets error message: "Method ShowAllData of object '_Worksheet' failed.

In fact row is copied to another sheet but the table in another sheet increases its range to hundred thousands of empty lines.

Below you can see my code. Please, advise me what can I fix here.

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets("Comments_database")
Set ws2 = Worksheets("PO'sDailyMonitor")

Application.ScreenUpdating = False

Worksheets("PO'sDailyMonitor").Range("P9").AutoFilter Field:=16, Criteria1:="<>"
ws2.Range("A10:Q10", Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Application.CutCopyMode = False

ws2.ShowAllData
ws2.Range("P10:P10000").ClearContents

Application.ScreenUpdating = True

MsgBox ("Database updated")

Hubert S · Accepted Answer · 2022-11-20 15:48:53Z

0

I solved it.

Just replaced this:

ws2.Range("A10:Q10", Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Copy

with:

ws2.ListObjects("PO_Query4").ListColumns(1).DataBodyRange.Resize(, 17).Select

answered Nov 20, 2022 at 15:48

Hubert S

779 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA: Copy last row from table cause error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related