created Table now vba code does not work anymore

Sjon1974

New Member
Joined
Apr 1, 2020
Messages
38
Office Version
  1. 365
Platform
  1. Windows
All,

I changed a worksheet into a table, but my import code does not work anymore.

VBA Code:
Sub Import()
   Dim ws1 As Worksheet, ws2 As Worksheet
   Dim LastRow As Long
   Dim Cl As Range
    Set ws1 = ThisWorkbook.Sheets("Import")
   Set ws2 = ThisWorkbook.Sheets("Database")
   LastRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
   ws1.Range("A7").AutoFilter 1, "*"
   ws1.AutoFilter.Range.Offset(1).EntireRow.Copy ws2.Range("a" & LastRow + 1)
   ws1.AutoFilterMode = False
   Call deleteBlankRows
   End Sub

Sub deleteBlankRows()
   Dim ws1 As Worksheet, ws2 As Worksheet
   Dim LastRow As Long
   Dim Cl As Range
   Set ws2 = ThisWorkbook.Sheets("Database")
Application.ScreenUpdating = False
ws2.Columns("G:G").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

I am refering to WS2 "Database" when i changed into a Table the macro is not working anymore.

What is going wrong:

Excel completly freezes and at the end is creating a 1004 error, so i have to change the range or something
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
It's possibly deleting the blanks that is causing it, if you filter the blanks before copying then you wouldn't have that problem.

Better to copy only the columns that you need as well, not the entire row, otherwise you're creating blank columns that need deleting as well.
 

Sjon1974

New Member
Joined
Apr 1, 2020
Messages
38
Office Version
  1. 365
Platform
  1. Windows
Yup that was it, i need an extra script for when i do multiple periods importing. But this was a single period

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,126,953
Messages
5,621,802
Members
415,857
Latest member
braunReivn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top