HOw do you save a file using a macro?
 

I'm trying to save a file with a macro. I have 90 different files that
I'm running the same macro on. When it's done I want the macro to save
it but with a different name.

Right now the code is setting the name of the file to the first name I
put in (e.g. CMAC301). I want the next file to be named CMAC302. How
do I do that?

When I had Office 97, I was able to somehow pause the macro, input data
as I needed and then resume. Is this still available?

HELP!!!!!!!!!!!!!!!!!!!!!!



------------------------------------------------
Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/

-- View and post usenet messages directly from http://www.ExcelTip.com
-- Hundreds of free MS Excel tips, tricks and solutions
------------------------------------------------

HOw do you save a file using a macro?
 

-----Original Message-----
I'm trying to save a file with a macro. I have 90
different files that
I'm running the same macro on. When it's done I want the
macro to save
it but with a different name.

Right now the code is setting the name of the file to the
first name I
put in (e.g. CMAC301). I want the next file to be named
CMAC302. How
do I do that?

When I had Office 97, I was able to somehow pause the
macro, input data
as I needed and then resume. Is this still available?

HELP!!!!!!!!!!!!!!!!!!!!!!



------------------------------------------------
Message posted from the Excel Tip Forum at
http://www.ExcelTip.com/forum/

-- View and post usenet messages directly from
http://www.ExcelTip.com
-- Hundreds of free MS Excel tips, tricks and solutions
------------------------------------------------
.


Not sure if I quite follow what you need to do - see if
this sounds useful..

If the files are created one at a time and you need to
save each one as you go then you need a way to track the
counter - here I've got a Workbook called Central where I
input the "CMAC" text and the start of the count. Each
time the macro is run the counter is advanced and the
central worksheet is updated for next time. I'd put the
macro code in this central workbook.

dim wb as workbook
dim wbCENTRAL as workbook
Dim ws as worksheet
dim strNAME as string
dim intCOUNT as integer

set wb = ActiveWorkbook
set wbCENTRAL = Workbooks("CENTRAL.xls")
set ws = wbcentral.activesheet

strNAME = ws.cells(1,1).value '''Storage for "CMAC"
intcount = ws.cells(1,2).value '''Storage for Counter

wb.SaveAs("C:\My Documents\" & strname & intcount & ".xls")
intCOUNT = intCOUNT+1
ws.cells(1,2).value = intcount '''Resets the counter for
next time

Cheers

Matthew

HOw do you save a file using a macro?
 

two possibilities...

1)

Sub SaveFileAs()
Dim sFileName As String
sFileName = Application.GetSaveAsFilename
If sFileName = "False" Then Exit Sub
ActiveWorkbook.SaveAs sFileName
End Sub

2)

Sub SaveFileAs()
Dim sFileName As String
sFileName = InputBox("Enter Name to be saved as...")
If sFileName = "" Then Exit Sub
ActiveWorkbook.SaveAs sFileName
End Sub


just a couple of ideas
you could also add
ActiveWorkbook.Close False
before the End Dub to close the file after saving it.

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
I'm trying to save a file with a macro. I have 90
different files that
I'm running the same macro on. When it's done I want
the macro to save
it but with a different name.

Right now the code is setting the name of the file to
the first name I
put in (e.g. CMAC301). I want the next file to be named
CMAC302. How
do I do that?

When I had Office 97, I was able to somehow pause the
macro, input data
as I needed and then resume. Is this still available?

HELP!!!!!!!!!!!!!!!!!!!!!!



------------------------------------------------
Message posted from the Excel Tip Forum at
http://www.ExcelTip.com/forum/

-- View and post usenet messages directly from
http://www.ExcelTip.com
-- Hundreds of free MS Excel tips, tricks and solutions
------------------------------------------------
.

HOw do you save a file using a macro?
 

kelleygirl,

You can do it either way.
If you put it into an existing macro, get rid of the Sub xxxx() and End Sub
Or you can call it from a sub by putting in a line

*code here*
Call xxxx
*more code here*

or Run xxxx
(I'm never sure which is correct)

I usually use
ModuleName.MacroName
to be more specific.
I also usually rename my modules with the property window. But never,
never, never, ..... name a module and a macro the same.

--
sb
"kelleygirl" wrote in message
...
Thak you very much.

One question, though.

Can this be added to the existing macro or does it have to be a
separate one?

Adding "sub" seems to separate them.



------------------------------------------------
Message posted from http://www.ExcelForum.com/

-- View and post Excel related usenet messages directly from
http://www.ExcelForum.com
-- Hundreds of free MS Excel tips, tricks and solutions at
http://www.ExcelTip.com/
------------------------------------------------