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,
Microsoft Excel

Avatar of undefined
Last Comment
W.E.B

8/22/2022 - Mon
ChloesDad

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
W.E.B

ASKER
Hello,
I get name saved as TR QB.xlsx
no date.
terencino

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
W.E.B

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

thanks
ChloesDad

Terencino, I had assumed that Cell K3 was on the current sheet
ASKER CERTIFIED SOLUTION
terencino

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
W.E.B

ASKER
Sweet,
thank you very much,
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
W.E.B

ASKER
Thank you.