Macro should Save and Overwrite file

J

Jon Dibble

Hi
This macro should run and just overwrite the original file. However when you
run it - it asks if you wish to save changes to "bcms_1002.xlsm" if you
click cancel the macro completes fine.
I need it to just run and save without this prompt.
Can someone please check my code?
Would be really appreciated.
Many thanks.


Sub bcms1002()
'
' bcms1002 Macro
'

'
Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_agent_1002_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
Range("A1").Select
ActiveWorkbook.Close
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Rows("17:18").Select
Selection.Delete Shift:=xlUp
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1:E16").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1:A16").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
1), Array(13, _
1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!R1C1:R12C2,2,FALSE)"
Selection.AutoFill Destination:=Range("D1:D16")
Range("D1:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("D1").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D16")
Range("D1:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:M").Select
Selection.ColumnWidth = 28.71
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "LOGIN"
Range("C1").Select
ActiveCell.FormulaR1C1 = "NAME"
Range("D1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ACD CALLS"
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Range("G1").Select
ActiveCell.FormulaR1C1 = "TOTAL AFTER CALL"
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL AVAIL"
Range("I1").Select
ActiveCell.FormulaR1C1 = "TOTAL AUX"
Range("J1").Select
ActiveCell.FormulaR1C1 = "EXTN CALLS"
Range("K1").Select
ActiveCell.FormulaR1C1 = "AVG EXTN TIME"
Range("L1").Select
ActiveCell.FormulaR1C1 = "TOTAL STAFFED"
Range("M1").Select
ActiveCell.FormulaR1C1 = "TOTAL HOLD"
Cells.Select
Cells.EntireColumn.AutoFit
With Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Y:\bcms_1002.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
End Sub
 
D

Dave Peterson

If you're overwriting the original file, then you should be able to just use
..Save:

ActiveWorkbook.Save
or
ThisWorkbook.Save

If you want to save as a new name, then use .saveas:

application.displayalerts = false
activeworkbook.saveas ....
application.displayalerts = true




Jon said:
Hi
This macro should run and just overwrite the original file. However when you
run it - it asks if you wish to save changes to "bcms_1002.xlsm" if you
click cancel the macro completes fine.
I need it to just run and save without this prompt.
Can someone please check my code?
Would be really appreciated.
Many thanks.

Sub bcms1002()
'
' bcms1002 Macro
'

'
Set NewSht = ThisWorkbook.ActiveSheet
ChDir "Y:\"
Set CSVFile =
Workbooks.Open(Filename:="Y:\report_list_bcms_agent_1002_.csv")
CSVFile.ActiveSheet.Range("A1:U20").Copy _
Destination:=NewSht.Range("A1")
Application.CutCopyMode = False
CSVFile.Close
Range("A1").Select
ActiveWorkbook.Close
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Rows("17:18").Select
Selection.Delete Shift:=xlUp
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1:E16").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1:A16").Select
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(8,
1), Array(13, _
1), Array(17, 1), Array(19, 1)), TrailingMinusNumbers:=True
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").Select
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "JAN"
Range("A2").Select
ActiveCell.FormulaR1C1 = "FEB"
Range("A3").Select
ActiveCell.FormulaR1C1 = "MAR"
Range("A1:A3").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B1:B3").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet2!R1C1:R12C2,2,FALSE)"
Selection.AutoFill Destination:=Range("D1:D16")
Range("D1:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("D1").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-2],RC[-1],RC[-3])"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D16")
Range("D1:D16").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:M").Select
Selection.ColumnWidth = 28.71
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "LOGIN"
Range("C1").Select
ActiveCell.FormulaR1C1 = "NAME"
Range("D1").Select
ActiveCell.FormulaR1C1 = "TIME"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ACD CALLS"
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG TALK TIME"
Range("G1").Select
ActiveCell.FormulaR1C1 = "TOTAL AFTER CALL"
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL AVAIL"
Range("I1").Select
ActiveCell.FormulaR1C1 = "TOTAL AUX"
Range("J1").Select
ActiveCell.FormulaR1C1 = "EXTN CALLS"
Range("K1").Select
ActiveCell.FormulaR1C1 = "AVG EXTN TIME"
Range("L1").Select
ActiveCell.FormulaR1C1 = "TOTAL STAFFED"
Range("M1").Select
ActiveCell.FormulaR1C1 = "TOTAL HOLD"
Cells.Select
Cells.EntireColumn.AutoFit
With Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Y:\bcms_1002.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complie Syntax Error 6
Macro not returning correct data 4
Different Results from the Same Macro 2
criteriarange not equal to 4
excel macro missing worksheets 9
File Name changes with dates and Deleting Rows of Zeros 0
variable number of rows. 2
Insert columns and values programmatically with IF statement 9

Top