Saving an excel file by using a macro

[Amaarouf]

Hi,

I'm new to VBA, but I'm trying to create a macro that will save an excel file as a cell name. The code that I have written so far is:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisFile = Range("V4").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
ActiveWorkbook.SaveAs Filename:="W:SharedDummyMTC Tracking SheetMTC" & ThisFile
End Sub

The problem is when I place this code in 'ThisWorkbook' under BeforeSave I get the following error msg:

'Excel.exe has generated errors and will be closed by Windows. You will need to restart the program, an error log has been created'

does anyone know what I might be doing wrong?

Thanks

 

[Andrew Poulsom]

What is in cell V4?

Do you really want to save the workbook twice - once in the current directory and then in W:SharedDummyMTC Tracking SheetMTC?

 

[Amaarouf]

Hi,

In V4 is an equation

=Sheet15!A1

I guess I only really need to save it once in W:SharedDummyMTC Tracking SheetMTC

thanks

 

[Iridium]

Try this:
Public Sub SaveAsA1()
'Saves file as what is in cell "A1" to specified network location

Dim Location, ThisFile As String

Location = "\directorysubdirectory"
ThisFile = Range("A1")
ThisFile = Location + ThisFile
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

Chris

 

[Andrew Poulsom]

What is the result of the formula:

=Sheet15!A1

in cell V4?

 

[Amaarouf]

the result is just a four digit number

 

[klb]

I have this in a module in my personal.xls. It will save the file to whatever name you have in cell A1. It will save it to the current directory unless you have a full path in A1.

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
If ThisFile = "" Then Exit Sub
ActiveWorkbook.SaveAs FileName:=ThisFile

End Sub