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

Afro_Cookie

Board Regular
Joined
Mar 17, 2020
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
UNTESTED
VBA Code:
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

Board Regular
Joined
Mar 17, 2020
Messages
65
Office Version
  1. 365
Platform
  1. Windows
UNTESTED
VBA Code:
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
VBA Code:
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
 
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad it worked for you... :cool: (y)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,116
Messages
5,768,211
Members
425,459
Latest member
Danniey

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