waza1
asked on
Need 'Save As' capability in attached VBA script.
Hi all, I am currently using the below code to extract data from various sources workbooks, and place into a single destination workbook. Problem is, although a unique name is given (todays date), unfortunately any subsequent files created on the same day are given the same name and are replacing the orginal file. I do not want this to happen, and would like any subsequent files be given the option of either replacing or a save as. With 'screen updating' on, it sort of works, but when you enter OK, it errors out. Any assistance given will be appreciated. Many thanks.
Sub SScopy()
Application.ScreenUpdating = False
Range("H6:R6").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>x", Operator:=xlAnd
Range("H6:AY225").Select
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteColumnWidths
Range("A1").PasteSpecial xlPasteValuesAndNumberForm ats
Range("A1").PasteSpecial xlPasteFormats
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs FileName:="\\Ooc-fls-01\CP SU\CPSU MASTER\New Invoicing\Invoice Batches\" & Format(Date, "yyyy mmmm dd") & ".xls"
ActiveWorkbook.Close
End Sub
Sub SScopy()
Application.ScreenUpdating
Range("H6:R6").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>x", Operator:=xlAnd
Range("H6:AY225").Select
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteColumnWidths
Range("A1").PasteSpecial xlPasteValuesAndNumberForm
Range("A1").PasteSpecial xlPasteFormats
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs FileName:="\\Ooc-fls-01\CP
ActiveWorkbook.Close
End Sub
ASKER
Great idea, but really need the ability to do a replace or save as.
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thankyou, thankyou, works like a charm.
ASKER
Almost forgot to forward the grade, sorry for that.
ActiveWorkbook.SaveAs FileName:="\\Ooc-fls-01\CP