Macro crashes with button, works perfectly if run line by line

[Afro_Cookie]

I'm using Office 365 and am importing data from an external database, creating a query as I do. I then have a macro that does some formatting and copies the table, without headers, to my main table. The code works perfectly if I open the editor and run it line by line. When I create a button to automate the process it crashes at the part where it selects the table, without headers.

This is my current code and the highlighted portion is the part that crashes when it's automated, but works perfectly when I run it line by line.

Is this a common thing? Is there an alternate method to select the contents of the table, without the headers? The first three columns of the table are blank and need to be.

Set tbl = ActiveCell.CurrentRegion

    Sheets("Sheet1").Select
    
    Range("B:C, E:E").Select
    Selection.ClearContents
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AC:AC").Select
    Selection.Delete Shift:=xlToLeft
    
    ActiveSheet.Columns(5).Insert
    
    tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select

    Selection.Cut
    Sheets("Table").Select
    Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    
    ActiveWorkbook.Queries("Q1").Delete
    Sheets("Sheet1").Delete
    
End Sub

 

[Michael M]

UNTESTED

Sub MM1()
Sheets("Sheet1").Select
Set tbl = ActiveCell.CurrentRegion
Range("B:C, E:E").ClearContents
Range("A:A, AC:AC").Delete
Columns(5).Insert
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Copy Sheets("Table").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0)
tbl.Resize(tbl.Rows.Count, tbl.Columns.Count).Delete
ActiveWorkbook.Queries("Q1").Delete
Sheets("Sheet1").Delete
End Sub

 

[Afro_Cookie]

UNTESTED

Sub MM1()
Sheets("Sheet1").Select
Set tbl = ActiveCell.CurrentRegion
Range("B:C, E:E").ClearContents
Range("A:A, AC:AC").Delete
Columns(5).Insert
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Copy Sheets("Table").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0)
tbl.Resize(tbl.Rows.Count, tbl.Columns.Count).Delete
ActiveWorkbook.Queries("Q1").Delete
Sheets("Sheet1").Delete
End Sub

Works very well. It still crashed at column delete lines but after I split it into two separate actions it resolved itself. Below is the completed functional code. Thanks Michael

Sub S0rt()

Set tbl = ActiveCell.CurrentRegion

    Sheets("Sheet1").Select
    Range("B:C, E:E").ClearContents
    Range("A:A").Delete
    Range("AC:AC").Delete
    Columns(5).Insert

    tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Copy Sheets("Table").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0)
    tbl.Resize(tbl.Rows.Count, tbl.Columns.Count).Delete
    
    ActiveWorkbook.Queries("Q1").Delete
    Sheets("Sheet1").Delete
    
End Sub

 

[Michael M]

Glad it worked for you...