Copy template to new book and save as based on cells

[missfionaelizabeth]

Hi

I have a sheet that I want to use as a template that is filled in via userform. I cannot for the life of me figure out why the code to save the workbook won't work, so maybe somebody here can help!!

Once the form has been filled in I have a section of code to copy the template sheet into a new workbook. I want the new workbook to Save As using the date and values from cells as the filename to a specific and static location. This is what I have. It appears to work, but I go to the location and there’s no file there. If I change anything at all it tells me that ‘SaveAs failed’.

I’ve tried different pieces of code that I’ve found around the internet.

The code is:

    Sheets("TEMPLATE").Select    Sheets("TEMPLATE").Copy
    
     FName = "Filepath is here " & Range("h2").Value & ".xlsm"
    ActiveWorkbook.SaveAs fileName:=FName, _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled
           ActiveWorkbook.Close

Can anyone see where I’m going wrong? Or is there a different way I can set up the sheet to make this work better?

All help would be greatly appreciated.

 

[dave3009]

Hi

Is the new workbook getting saved to the same location as TEMPLATE workbook or somewhere different. If it's somewhere else what is the full path to that folder.

Thanks

Dave

 

[missfionaelizabeth]

Hi Dave

It's getting save somewhere else.

The full path is:

F:\els-serv\C01A\Central Admin Lifelong Learning\Finance\FINANCE OFFICER\Fiona Brown\Refunds\New Refunds\

 

[Norie]

What do you actually have for "Filepath is here "?

Does it end in a \?

 

[dave3009]

As Norie has pointed out, you probably want to put that folder path in where your code says "Filepath is here"

Does this work?

 

[missfionaelizabeth]

In the code I'm actually running I have the filepath in already. I wasn't sure if I should be posting our filepaths on the internet or if long strings would annoy people so replaced it with the marker.

In the code I am running, it does end in a \, and it still doesn't work

 

[dave3009]

So next thing to check is what is housed in Range("h2").Value if it contains an invalid characters (\ / : * ? " < > | ) then it will run into problems.

Is this the case?

 

[Norie]

Are you trying to save to a folder on a network where F is a mapped drive?

 

[missfionaelizabeth]

Hi again

H2 contains references to other cells because I wanted the title of the file to be the Learner ID (B11) and the course code (B13).

I changed the code to reference B11 instead of H2 to test if it was the cell content that was the issue and the macro worked perfectly, saving the file to the right place and naming it as the value of the cell. I tried the following code and again it stopped working::

 FName = "F:\els-serv\C01A\Central Admin Lifelong Learning\Finance\FINANCE OFFICER\Fiona Brown\Events\" & Range("B7").Value & " " & Range("B13").Value & ".xlsm"    ActiveWorkbook.SaveAs fileName:=FName, _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled

Is there a way to get this working?

thank you so much for your help so far. I don't know what else to try!!

 

[Norie]

Which sheet are the cells, B7 and B13, with the filename on?