ExcelBanter - Save as saves selected worksheet instead of whole workbook

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Save as saves selected worksheet instead of whole workbook (https://www.excelbanter.com/excel-programming/363272-save-saves-selected-worksheet-instead-whole-workbook.html)

Save as saves selected worksheet instead of whole workbook

Hi -

I have a macro that does a save as action and saves as a name from the
values of a couple cells... and it seems to work okay. The problem is I
have 3 worksheets and it only saves the active worksheet... can anybody
help me out. It would be much appreciated.

This is what I've got

Sub SaveAs()

Dim uname

With ActiveWorkbook.Worksheets("sheet1")
uname = .Range("B2").Value & " " & _
.Range("B3").Value
End With
ActiveWorkbook.SaveAs ThisWorkbook.Name _
& " " & " " & uname & ".xls"
ActiveWorkbook.Close True
End Sub

Again, I need it to save the entire workbook to a new file and not just
the avctive worksheet.
Thanks
A

Save as saves selected worksheet instead of whole workbook

Hi Alex,

Your code saves the entire workbook for me.

If a single-sheet workbook is being saved, check that your code does not
include an intervening line like:

Activesheet.Copy

Such a code line would create a new, single-sheet workbook and this new
workbook would become the active workbook.

---
Regards,
Norman

wrote in message
oups.com...
Hi -

I have a macro that does a save as action and saves as a name from the
values of a couple cells... and it seems to work okay. The problem is I
have 3 worksheets and it only saves the active worksheet... can anybody
help me out. It would be much appreciated.

This is what I've got

Sub SaveAs()

Dim uname

With ActiveWorkbook.Worksheets("sheet1")
uname = .Range("B2").Value & " " & _
.Range("B3").Value
End With
ActiveWorkbook.SaveAs ThisWorkbook.Name _
& " " & " " & uname & ".xls"
ActiveWorkbook.Close True
End Sub

Again, I need it to save the entire workbook to a new file and not just
the avctive worksheet.
Thanks
A

Save as saves selected worksheet instead of whole workbook

Actually, I tried your first example and it saved the entire workbook
just as you wanted.

Did you have any code you wanted to save in your Personal .xls before
you copied over it?

Save as saves selected worksheet instead of whole workbook

You told it

With ActiveWorkbook.Worksheets("sheet1")
uname = .Range("B2").Value & " " & _
.Range("B3").Value
End With
ThisWorkbook.SaveAs ThisWorkbook.Name _
& " " & " " & uname & ".xls"
ActiveWorkbook.Close True
End Sub

?

Just save it as "Personal.xls"

In your XLStart folder

"C:\Documents and Settings\YourName\Application
Data\Microsoft\Excel\XLSTART\PERSONAL.XLS"

Save as saves selected worksheet instead of whole workbook

Hi Everyone - thanks for responding...

Here is the code again....

Sub SaveAs()
Dim uname

With ActiveWorkbook.Worksheets("sheet1")
uname = .Range("B2").Value & " " & _
.Range("B3").Value
End With
ActiveWorkbook.SaveAs ThisWorkbook.Name _
& " " & " " & uname & ".xls"
ActiveWorkbook.Close True
End Sub

What I am doing is dropping a .txt file into Excel and then running one
big macro that contains a bunch of little macros. I want the last macro
to name the file the value of cell B2 (which is the word "Subject") and
B3 (which is a number, like 105), than save as xls to the default excel
location, then close it.

If I used ActiveWorkbook.SaveAs, it saves the file as Personal.xls
Subject 105 to the default excel location but it only contains one of
the worksheets - the last worksheet the macro did its work in - instead
of the whole workbook.

If I used ThisWorkbook.SaveAs, it saves the file as Personal.xls
Subject 105 but this time it actually saves the Personal.xls file to
the default location. Then if I go to Window--Unhide, the hidden
window has now turned into Personal.xls Subject 105.

Any help you guys could offer would be much appreciated...

alex

Save as saves selected worksheet instead of whole workbook

Just for clarification, I don't understand why it is saving it as
personal.xls at all - I don't want to do anything with Personal.xls - I
just need it because it contains my macros.

Save as saves selected worksheet instead of whole workbook

Sorry, I thought you needed to get your Personal.xls back.

Save as saves selected worksheet instead of whole workbook

Your code still does just what you say you want to - are you sure
you're not copying or moving a sheet somewhere before the lines you've
posted?

ThisWorkbook always refers to the workbook the code is in; if you run
code from Personal.xls, then ThisWorkbook.Name is Personal.xls.

Save as saves selected worksheet instead of whole workbook

Thanks for that tip.

Weird... I just tried it on another computer and I still had my
problem... the weirdest part is that in the beginning of the code,
where is says ActiveWorkbook.Worksheets ("Sheet1") and tells it to grab
the data - Sheet 1 is the sheet that is missing when I open it up after
it's been saved.

Do you have any idea why when I do activeworkbook it is still naming it
personal.xls subject 105 - I don't understand why it's adding that?

A workaround I thought of is, is there a macro that will copy
everything from the personal.xls onto the activeworksheet and then run
the code from the activeworksheet. I hoped to just hit one Ctl+D and
have it run, save and shut without hitting another button

Save as saves selected worksheet instead of whole workbook

"I want the last macro to name the file the value of cell B2 (which is
the word "Subject") and B3 (which is a number, like 105), than save as
xls to the default excel location, then close it."

First search your module(s) for ".copy" and/or ".move" without the
quotes. If you find anything, there's why you're getting just one
sheet.

If you just want what I've quoted above and you cleared up the
copy/move deal, just eliminate the "ThisWorkbook" part from the lines
you've posted.

Sub SaveAs()

Dim uname

With ActiveWorkbook.Worksheets("sheet1")
uname = .Range("B2").Value & " " & _
.Range("B3").Value
End With

With ActiveWorkbook
.SaveAs uname & ".xls"
.Close True
End With

End Sub

Save as saves selected worksheet instead of whole workbook

Hi Alex,

ActiveWorkbook.SaveAs ThisWorkbook.Name _

If, as seems very probable, the code of interest is housed in your
Personal.xls file, you will encounter the scenario that you describe:

If I used ActiveWorkbook.SaveAs, it saves the file as Personal.xls
Subject 105 to the default excel location

If, instead you use:

If I used ThisWorkbook.SaveAs,

The file that will be saved is the workbook holding the code, and thus:

it saves the file as Personal.xls
Subject 105 but this time it actually saves the Personal.xls file

With the (multi-sheet) file of interest the active workbook, your
instruction should be of the form:

With ActiveWorkbook
.SaveAs Filename:=.Name & " " & " " & uname & ".xls", _
FileFormat:=xlWorkbookNormal
End With

Better still, set the workbook of interest to an object variable, e.g.:

Dim WB As Workbook

Set WB = YourTextFile
or
Set WB = ActiveWorkbook (When the text file is first opened and
active)

Then you could save the file with an instruction like:

With WB
.SaveAs Filename:=.Name & " " & " " & uname & ".xls", _
FileFormat:=xlWorkbookNormal
End With

---
Regards,
Norman

wrote in message
oups.com...
Hi Everyone - thanks for responding...

Here is the code again....

Sub SaveAs()
Dim uname

With ActiveWorkbook.Worksheets("sheet1")
uname = .Range("B2").Value & " " & _
.Range("B3").Value
End With
ActiveWorkbook.SaveAs ThisWorkbook.Name _
& " " & " " & uname & ".xls"
ActiveWorkbook.Close True
End Sub

What I am doing is dropping a .txt file into Excel and then running one
big macro that contains a bunch of little macros. I want the last macro
to name the file the value of cell B2 (which is the word "Subject") and
B3 (which is a number, like 105), than save as xls to the default excel
location, then close it.

If I used ActiveWorkbook.SaveAs, it saves the file as Personal.xls
Subject 105 to the default excel location but it only contains one of
the worksheets - the last worksheet the macro did its work in - instead
of the whole workbook.

If I used ThisWorkbook.SaveAs, it saves the file as Personal.xls
Subject 105 but this time it actually saves the Personal.xls file to
the default location. Then if I go to Window--Unhide, the hidden
window has now turned into Personal.xls Subject 105.

Any help you guys could offer would be much appreciated...

alex

Save as saves selected worksheet instead of whole workbook

OK - I figured out what's going on, I think. First of all the line that
says
ActiveWorkbook.SaveAs ThisWorkbook.Name - the ThisWorkbook part was
the reason it was saving with the words Personal.xls at the beginning.

And the reason it's isn't saving all the workbooks is because it's a
text file that is just dropped in an Excel doc so it has to be properly
saved as an XLS FIRST, then the macro can add the sheets and then it
will save the whole thing. Thanks for you help guys