Macro to add code to ThisWorkbook module

  • Thread starter Thread starter excelnut1954
  • Start date Start date
E

excelnut1954

From an end of day macro, a sub performs a Save-As to creates a new
file name for the same data. Can I add code to the new file
ThisWorkbook module that will also perform a Save-As to create a third
file?

Ex: Macro in file First.xls performs Save-As to create file
Second.xls.
The same macro will add code in ThisWorkbook module of Second.xls that
will perform Save-As to create file named Third.xls.

Is this possible? I'm trying to do a work around that will free up the
file Second.xls when users open it up. This, so an end of day macro
can update Second.xls even if opens up that file before the report is
run.

Hope this makes sense.
Thanks
j.o.
 
Hi ExcelNut,

See |Chip Pearson at::

        Programming The VBA Editor
       http://www.cpearson.com/excel/vbe.aspx

---
Regards.
Norman









- Show quoted text -
Click to expand...

Thanks, Norman.
I appreciate your reply. I went in Chip's site, and I think I found
what I needed. But, it looks like there are a few Excel settings that
would need to be changed in all the computers that would access this
file. That's impossible here. The settings on many programs in all the
computers here are all restored to default mode. Even Excel is
restored to default mode each night in all the computers. Really
sucks.

But, I found an old post that I adapted to help in a work around. It's
an If-then for the ThisWorkbook module. Very simple.

Sub fndFileNm()
fname = ActiveWorkbook.Name

Application.DisplayAlerts = False
If fname = "Current Furniture Staging List - testing.xls" Then
ActiveWorkbook.SaveAs _
Filename:="\\ceddfssrv01\cedroot\public\furniture staging list" &
"\Current Furniture Staging List - MacroTest.xls"
Application.DisplayAlerts = True

End If
End Sub

I tested it, and it seems to work. I'll try it in the live version of
the file tomorrow.
 
Back
Top