VBA Code - pick file name from cell value

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,475
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

This is part of my macro which I am using.

Code:
Sub makecopy()

    Workbooks.Add
    ChDir "C:\BACKUP"
    ActiveWorkbook.SaveAs Filename:="C:\Backup\[B][COLOR=#ff0000]Factory Target[/COLOR][/B].xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False  
    
End Sub

Can I tell the code to pick file name from cell value & some free defined text in the code.

For Example

Cell A1 = "Honda"
Cell B2 = "Civic"

I would like the file name to be "MAKE Honda MODEL Civic"

something like this
"MAKE" & Range A1.value & "MODEL" & Range B2.Value



Any help would be appreciated

Regards,

Humayun
 
Thanks Joe

I tried but still facing same problem
Same error msg & same line debugging
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Did you confirm that you are able to save files to that directory shown?
Can you post the full file name that is being returned to the screen?
 
Upvote 0
If you run this, what (if anything) does the msgbox say
Code:
Sub Chk()
   MsgBox Dir("C:\Backup", vbDirectory)
End Sub
 
Upvote 0
I get a msg box BACKUP
Can you please answer the questions I had up in post 12?
 
Last edited:
Upvote 0
Can you please answer the questions I had up in post 12?

Yes sure why not



Did you confirm that you are able to save files to that directory shown?

Yes

Can you post the full file name that is being returned to the screen?

I get this msg box
C:\BACKUP\CustomerEberleSupplierAll Supplers20190912203533.xlsx


I would request you guys to please again look at the full code... Just to make sure that there is nothing wrong with the code

Code:
Sub makecopy()

Application.DisplayAlerts = False




   Dim fName As String
    fName = "C:\BACKUP\Customer" & Range("A1").Value & "Supplier" & Range("F1").Value & Format(Now, "yyyymmddhhmmss") & ".xlsx"
    MsgBox fName


    Workbooks.Add
    ChDir "C:\BACKUP"
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    
    Windows("REPORTS").Activate
    Sheets("FACTORY TARGETS").Select
    
     Range("A1:AF1000").Select
    Selection.copy
    
    Windows("Factory Target.xlsx").Activate
    
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
        
    Range("A3:AF3").Interior.ColorIndex = 15
    
    Range("D:D,L:L,P:S,V:W,AB:AE").Delete
    
    Columns("J:S").ColumnWidth = 12
    Columns("T:T").ColumnWidth = 45
    
    Rows("1:2").RowHeight = 34.5
    Rows("3:3").RowHeight = 63.5
    Rows("4:1000").RowHeight = 24.75
    
    ActiveWindow.DisplayGridlines = False
    Cells.Validation.Delete
     
    ActiveWorkbook.Close SaveChanges:=True
    
    Windows("REPORTS.xlsm").Activate
    
    Application.DisplayAlerts = True
    
End Sub
 
Upvote 0
Yes sure why not
Yes, please do if you want my help.

What exactly is this code copying?
The current file?
If so, and this VBA code is in that current file, you cannot save it as an "xlxs" file and it will need to be saved to an extension that allows macros/VBA, i.e. "xlsm".

I would request you guys to please again look at the full code... Just to make sure that there is nothing wrong with the code
If what you said up in post 7 is accurate, that it is getting hung up on the "save" line, then anything under that isn't coming into play yet. So nothing below that line would affect it.
 
Last edited:
Upvote 0
Hi Fluff,

I get a msg box BACKUP

Ok that means that the directory exists.
Do you want to save the file in that directory, or in something like C:\Backup\Customers\
 
Upvote 0
Thanks Joe for your help. I appreciate you are putting in your time to help me out.

What exactly is this code copying?
The current file?
If so, and this VBA code is in that current file, you cannot save it as an "xlxs" file and it will need to be saved to an extension that allows macros/VBA, i.e. "xlsm".

Well, the code is copying some data from a sheet in the current workbook (macro enabled) from which the code is being run to a new workbook & in that new workbook no macros or vba codes are there. It’s just simple excel file. I can save it as a simple .xlsx file.

All I want the code to do is open a new file and save it with a file name which could look at literal text parts and the variables/range references.
 
Upvote 0
Thanks Fluff - you have always helped me out in my number of threads. I appreciate

Ok that means that the directory exists.
Do you want to save the file in that directory, or in something like C:\Backup\Customers\

I want to save the file in C:\Backup
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top