This is my macro. When last workbook is closed I get run time error. But actually when last workbook is closed I want Excel to close. I will be running this macro on serveral workbooks and when the last workbook is closed I want Excel to close. Here is my macro
Sub EECHARTS()
'
' EECHARTS Macro
' AUTO CHART EE OVERALL DIAMETER DATA
'
' Keyboard Shortcut: Ctrl+p
'
' Rename Sheet
ActiveSheet.Name = "Data"
'
' Set Cell D1 Equal to the File Name
Range("$D$1") = ActiveWorkbook.Name
Range("D2").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R[-1]C,"".csv"","""")"
Range("D2").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlValues
Range("D2").Select
Selection.ClearContents
'
' Select Data Automatically
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "TableRange"
'
' Chart Data Selected Above
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Macro Chart"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("TableRange"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart.TextBoxes.Add(338, 229, 49, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$C$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft -75#
Selection.ShapeRange.IncrementTop -191.52
With ActiveChart.TextBoxes.Add(338, 229, 34, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$D$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft 136#
Selection.ShapeRange.IncrementTop -191.52
'
' Print Chart
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'
' Save File
If ActiveWorkbook.FileFormat = xlCSV Then
ActiveWorkbook.SaveAs FileFormat:=xlNormal
End If
'
' Close Workbook
ActiveWorkbook.Close
'
' Run Macro for all Open Files
Application.OnTime Now + TimeValue("00:00:01"), "EECHARTS"
'
End Sub
Thanks,
Chad
Sub EECHARTS()
'
' EECHARTS Macro
' AUTO CHART EE OVERALL DIAMETER DATA
'
' Keyboard Shortcut: Ctrl+p
'
' Rename Sheet
ActiveSheet.Name = "Data"
'
' Set Cell D1 Equal to the File Name
Range("$D$1") = ActiveWorkbook.Name
Range("D2").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R[-1]C,"".csv"","""")"
Range("D2").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlValues
Range("D2").Select
Selection.ClearContents
'
' Select Data Automatically
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "TableRange"
'
' Chart Data Selected Above
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Macro Chart"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("TableRange"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart.TextBoxes.Add(338, 229, 49, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$C$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft -75#
Selection.ShapeRange.IncrementTop -191.52
With ActiveChart.TextBoxes.Add(338, 229, 34, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$D$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft 136#
Selection.ShapeRange.IncrementTop -191.52
'
' Print Chart
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'
' Save File
If ActiveWorkbook.FileFormat = xlCSV Then
ActiveWorkbook.SaveAs FileFormat:=xlNormal
End If
'
' Close Workbook
ActiveWorkbook.Close
'
' Run Macro for all Open Files
Application.OnTime Now + TimeValue("00:00:01"), "EECHARTS"
'
End Sub
Thanks,
Chad