File Name

[Aaron]

If I have a file that over forty people will be using, how can I get my macros to run if the user decides to change the name of the file? If you want to see my code let me know, and I will post it. Any help would be greatly appreciated.

 

[Mark O'Brien]

Unless your code searches for that particular file (i.e. the workbook whose name may be changed) there shouldn't be a problem running the macros. I think you're going to have to show some of your code here.

Perhaps, just the code that is meant to start the code running.

 

[Aaron]

Thank you for the quick response. My code is really basic, and I apologize for that. I am merely learning the great capabilities of VBA.

Sub CreateExtract()
Workbooks.Add
Windows("MFGIndirectModeltest.xls").Activate
Sheets("FORECAST").Select
Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("A:A").EntireColumn.AutoFit
Columns("B:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1:A2000").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Range("A1").Select
ActiveWorkbook.SaveAs _
FileName:="D:Personal" & Worksheets("Sheet1").Range("A1").Value & "I.xls", _
FileFormat:=xlNormal, _
Password:="mfgie", _
WriteResPassword:=""
ActiveWindow.Close SaveChanges:=True
Windows("MFGIndirectModeltest.xls").Activate
Range("A30").Select
Sheets("Instructions").Select
End Sub

I apologize in advance for the code being so long, but I am just scratching the surface of the files

 

[Mark O'Brien]

That's cool, everyone has to start from somewhere, this part of the code is your problem:

<pre>
Workbooks.Add
Windows("MFGIndirectModeltest.xls").Activate </pre>

I would change this to something like:

<pre>
'Declare Variable
Dim sThisWorkbook As String

'Intialise variable
sThisWorkbook = ThisWorkbook.Name

Workbooks.Add
Windows(sThisWorkbook).Activate
</pre>

Then it shouldn't matter what the workbook is called, it's always going to use the workbook that the code is situatuated in.

HTH

 

[Aaron]

Mark,

Thanks for your help, that works fabulously. My next question in the same code above is, the Close Method: I have SaveChanges as True, how can I get the code to overwrite the file. Instead of having a dialogue Box pop up and ask me if I want to overwrite the existing file, just have it automatically overwrite the existing file. Again many thanks for your help.