VBA Code - pick file name from cell value

[hrayani]

Hello Friends,

This is part of my macro which I am using.

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

 

[hrayani]

Thanks Joe

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

 

[Joe4]

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?

 

[Fluff]

If you run this, what (if anything) does the msgbox say

Sub Chk()
   MsgBox Dir("C:\Backup", vbDirectory)
End Sub

 

[hrayani]

Hi Fluff,

I get a msg box BACKUP

 

[Joe4]

I get a msg box BACKUP

Can you please answer the questions I had up in post 12?

 

[hrayani]

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

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

 

[Joe4]

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.

 

[Fluff]

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\

 

[hrayani]

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.

 

[hrayani]

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