Excel Table - VBA Macro to automatically update formatting if a line is deleted.

[BekahBu]

The below macro takes data from the "OUTPUT_SPEC_PHARMACIES" tab and inserts it into a table on the "Pharmacy_Breakdown" tab. The table itself updates automatically if any new data is entered or deleted in the "OUTPUT_SPEC_PHARMACIES" tab. However, if data is deleted, the formatting of the table remains with nothing in it.

How can I change the macro to have the formatting update as well?

Thank you!!



Sub x()
Dim WS As Worksheet, WS2 As Worksheet
Set WS = ThisWorkbook.Worksheets("Pharmacy_Breakdown")
Set WS2 = ThisWorkbook.Worksheets("OUTPUT_SPEC_PHARMACIES")
Dim i As Long, j As Long
Dim num_rows As Long


num_rows = WorksheetFunction.CountA(WS2.Range("A:A"))
WS.Range("A6:H10000").ClearContents
For i = 5 To num_rows
For j = 1 To 8
WS.Cells(i, j) = WS2.Cells(i, j)
Next j
Next i




End Sub