VBA breaks when it comes to deleting command button and selecting A1 cell

[Alxnrk]

Hi,

I intend to use the following code to create a report (with values pasted only). It works just fine for me except for one moment - it breaks when it comes to deleting command button and selecting cell A1. I put additional comments thorough the code. Could you please advise on possible solutions?




Option Explicit


Sub SaveAs()


'I go to report.xlsm which is the starting point of report generation process. There I have two sheets, one with all the formulas "ratings_report" and another one "report" where values from "ratings_report" are going to be pasted and from which the final report is going to be generated.


Sheets("ratings_report").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("report").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("ratings_report").Select
Range("U2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("report").Select
Range("U2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("ratings_report").Select
Range("C7:AK52").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("report").Select
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Then, I prepare technical sheet for the next day`s report. If this block is excluded from the code (except ThisWorkbook.Save line, everything works fine [but important functional part is missing then])


Sheets("ratings_report").Select
Range("C7:S50").Select
Application.CutCopyMode = False
Selection.Copy
Range("U7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Range("U2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Save




'Saving as a new workbook and deleting technical sheet to leave only final report. But when I try to delete command button from "report" sheet, which is the only sheet that remains after deleting the technical one "rating_reports", the code breaks.


Dim fdate As Date
Dim fname As String
Dim path As String


fdate = Range("C2").Value
path = Application.ActiveWorkbook.path


If fdate > 0 Then
fname = fdate & ".xlsm"
Application.ActiveWorkbook.Sheets("report").SaveAs Filename:=path & "" & fname, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Else
MsgBox "Chose a date for the event", vbOKOnly
End If


Application.DisplayAlerts = False
Sheets("ratings_report").Delete
ActiveSheet.Buttons.Delete <=== THE CODE BREAKS
Range("A1").Select
ThisWorkbook.Save
Application.Quit




End Sub

[Paul_Hossler]

1. I added
tags to your post -- you can do that next time using the [#] icon and pasting the macro between the tags


2. You can't delete Buttons en mass like that

One at a time, even if there's only one

This is for Form buttons, not ActiveX type, since you didn't say which type of button it is


[CODE]
Do While ActiveSheet.Buttons.Count > 0
ActiveSheet.Buttons(1).Delete
Loop