Link to home
Start Free TrialLog in
Avatar of W.E.B
W.E.B

asked on

Save as vba / date

Hello,
Can you please help,
I'm trying to save the file based on a cell that contains date.

Sub SAVE_Range_XLS()

    Sheets("QB").Select
    Range("A1:H65000").Copy
    Sheets.Add.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    ActiveSheet.UsedRange.EntireColumn.AutoFit
    ActiveSheet.Move
   
    ActiveWorkbook.SaveAs FileName:="C:\Users\Wassim\Desktop\TR QB " & Format(Range("K3").Value, "yyyy-mm-dd") & ".xlsx"
    ActiveWorkbook.Close False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True
   
End Sub

Example,
file name  TR QB,  Cell K3 = 03/16/2013
Output should be save as   TR QB 03-16-2013

Also,
how do I save it as .txt

thanks,
Avatar of ChloesDad
ChloesDad
Flag of United Kingdom of Great Britain and Northern Ireland image

The code should work as is, if not then split it out like this. Add the fileformat property to save as text.

  filename = "C:\Users\Wassim\Desktop\TR QB " & Format(Range("K3").Value, "yyyy-mm-dd") & ".txt"
 
  ActiveWorkbook.SaveAs filename:=filename, FileFormat:=xlTextMSDOS
Avatar of W.E.B
W.E.B

ASKER

Hello,
I get name saved as TR QB.xlsx
no date.
Wassim, Because you have added a new sheet in the macro, that will become the active sheet. So if the K3 range is on sheet QB just modify your macro to
ActiveWorkbook.SaveAs FileName:="C:\Users\Wassim\Desktop\TR QB " & Format(Sheets("QB").Range("K3").Value, "yyyy-mm-dd") & ".xlsx"
...Terry
Avatar of W.E.B

ASKER

HI Terry,
Run-time error 9
Subscript out of range.

thanks
Terencino, I had assumed that Cell K3 was on the current sheet
ASKER CERTIFIED SOLUTION
Avatar of terencino
terencino
Flag of Australia 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 W.E.B

ASKER

Sweet,
thank you very much,
Avatar of W.E.B

ASKER

Thank you.