I am working file that will generate invoices and then delete its
information and the macro to make it smaller and simpler for email.
There are a few tricks I cannot seem to accomplish and I was hoping to
get some help.

The file takes the data from a website via copy and paste then pulls
the nessecary data from a few sheets in the workbook. Then there is a
button to run the macro which does the following:

- Replace the invoice formulas with the values itself - Done
- Delete the now unnessecary (3) sheets leaving only the invoice -
Done
- Delete the Button used to run the macro - Done
- Save as a filename in a particular cell in a particular subfolder -
Partial
- Have the macro delete itself - Done


The issues are the subfolder, it will save in "My Documents" as the
cell reference, but I cannot figure out how to also to get it in a
subfolder called "Proformas".
Also If possible I would like the saved file to have the macro deleted
w/o having to save the file on close. the saved version still has the
macro in the files unless i hit save or choose to keep changes on
close. These two little things are the only hurdles for this lil
project (so Far), any help will be appreciated.
Current Macro Below


Code:
--------------------
Sub GenerateProforma()
'
' GenerateProforma Macro
' Macro recorded 05/31/2006 by Curtis W Bennett Jr
'

'
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.DisplayAlerts = False
Sheets("Tool Data").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Tables and DBs").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Information").Select
ActiveWindow.SelectedSheets.Delete
Range("A10").Select
ActiveSheet.Shapes("Button 83").Select
Selection.Delete
ThisFile = Range("J19").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
Application.DisplayAlerts = True

Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("GenerateProforma")
End Sub
--------------------


--
Curben
------------------------------------------------------------------------
Curben's Profile: http://www.excelforum.com/member.php...o&userid=35874
View this thread: http://www.excelforum.com/showthread...hreadid=556646

Curben wrote:
- Save as a filename in a particular cell in a particular subfolder - Partial

The issues are the subfolder, it will save in "My Documents" as the
cell reference, but I cannot figure out how to also to get it in a
subfolder called "Proformas".

Why not just append "Proformas\" to the beginning of your Save path?

Also If possible I would like the saved file to have the macro deleted
w/o having to save the file on close. the saved version still has the
macro in the files unless i hit save or choose to keep changes on
close. These two little things are the only hurdles for this lil
project (so Far), any help will be appreciated.

You could always have the Module in a different workbook A, and all of
your functions operate on the file you opened (workbook B).

Current Macro Below


Code:
--------------------
Sub GenerateProforma()
Cells.Copy
Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
Sheets("Tool Data").Delete
Sheets("Tables and DBs").Delete
Sheets("Information").Delete
Application.DisplayAlerts = True

Range("A10").Select
ActiveSheet.Shapes("Button 83").Delete
ThisFile = Range("J19").Value
ActiveWorkbook.SaveAs Filename:= "Proformas\" & ThisFile

Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("GenerateProforma")
End Sub

"Orne" Wrote:
Why not just append "Proformas\" to the beginning of your Save path?
I have tried a few methods of doing that, none have worked while usin
the range and I am not nearly proficient enough to figure out any othe
ways of combining.

"Orne" Wrote:
You could always have the Module in a different workbook A, and all of
your functions operate on the file you opened (workbook B).
Will not work, this will be distributed to too many users at random an
to utilize multiple workbooks would counteract the goal of simplifing i
for the masses

--
Curbe
-----------------------------------------------------------------------
Curben's Profile: http://www.excelforum.com/member.php...fo&userid=3587
View this thread: http://www.excelforum.com/showthread.php?threadid=55664