Link to home
Start Free TrialLog in
Avatar of waza1
waza1Flag for Australia

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 xlPasteValuesAndNumberFormats
Range("A1").PasteSpecial xlPasteFormats
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs FileName:="\\Ooc-fls-01\CPSU\CPSU MASTER\New Invoicing\Invoice Batches\" & Format(Date, "yyyy mmmm dd") & ".xls"
ActiveWorkbook.Close
End Sub
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Here is one very simple change that guarantees a unique file name, unless you can somehow run the routine more than once in the same second:

ActiveWorkbook.SaveAs FileName:="\\Ooc-fls-01\CPSU\CPSU MASTER\New Invoicing\Invoice Batches\" & Format(Now, "yyyy-mm-dd hh-nn-ss") & ".xls"
Avatar of waza1

ASKER

Great idea, but really need the ability to do a replace or save as.
 Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of waza1

ASKER

Thankyou, thankyou, works like a charm.
Avatar of waza1

ASKER

Almost forgot to forward the grade, sorry for that.