John.McLaughlin
Board Regular
- Joined
- Jul 19, 2011
- Messages
- 169
The macro appears to delete all the columns, but stops the macro at the Column delete command?
(Run Time Error '1004. Delete method of range class failed)
Backstory: This is my daily "planner" workbook, it is copied to my tablet each morning. The macro first deletes unnecessary worksheets, and then deletes rows and columns on the remaining worksheet. The workbook is opened automatically when I start Excel on my tablet. The worksheet displays a chronologically sorted list of customers, whose names I can then touch and open their workbook.
(Run Time Error '1004. Delete method of range class failed)
Code:
Sub RouteSS()
Workbooks("SCHEDULED.xlsm").Activate
ActiveWorkbook.Save
Sheets("SCHEDULED").Visible = True
Sheets("SCHEDULED").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\data\Business\route\SCHEDULED.xlsm"
ActiveWorkbook.Close
' Open New Route Only Workbook
Workbooks.Open Filename:="C:\data\Business\route\SCHEDULED.xlsm"
Application.DisplayAlerts = False
Sheets("BUILDTHIS").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sales Meeting").Select
ActiveWindow.SelectedSheets.Delete
Sheets("To Do").Select
ActiveWindow.SelectedSheets.Delete
Sheets("To Do 2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("To Do 3").Select
ActiveWindow.SelectedSheets.Delete
Sheets("TASKS").Select
ActiveWindow.SelectedSheets.Delete
Sheets("PLANS").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Workbooks("SCHEDULED.xlsm").Activate
Sheets("SCHEDULED").Visible = True
Sheets("SCHEDULED").Select
ActiveSheet.Buttons.Delete
Range("H1:H37").UnMerge
Range("A1:Z1").Clear
Range("$C:$H").Select
' Below line is the Yellow highlighted code
Range("$C:$H").Delete
Rows("1:2").Select
Range("A1:B1").Select
Selection.ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Sheets("SCHEDULED").Select
Range("A2").Select
ActiveWindow.Zoom = 200
ActiveWorkbook.Save
ActiveWorkbook.Close
' Open scheduled workbook
Workbooks.Open Filename:="C:\data\SCHEDULED.xlsm"
ChDir "c:\"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheets("SCHEDULED").Visible = True
Sheets("SCHEDULED").Select
End Sub
Backstory: This is my daily "planner" workbook, it is copied to my tablet each morning. The macro first deletes unnecessary worksheets, and then deletes rows and columns on the remaining worksheet. The workbook is opened automatically when I start Excel on my tablet. The worksheet displays a chronologically sorted list of customers, whose names I can then touch and open their workbook.