-
Apr 26th, 2004, 11:10 AM
#1
Thread Starter
Hyperactive Member
Excel, save just a single worksheet to new XLS file [resolved]
How can I, from within the VBA code, save a single worksheet to a new .XLS file?
I tried the sheet.SaveAs filename method
but it saves the ENTIRE current file, all sheets and macros etc, into the new file.
I just want an XLS file with ONE sheet.
Here's what I'm doing now
Code:
Dim NewSheet As Worksheet
Dim NewSheetName As String
' Copy the original, target sheet to temp so we can operate on it
Worksheets(Original_Sheet_Name).Copy after:=Sheets(Sheets.Count)
Set NewSheet = Worksheets(ActiveSheet.Name)
NewSheet.Name = NewSheetName
(do stuff ...)
NewSheet.SaveAs FileName
Thanks, DaveBo
Last edited by DaveBo; Apr 27th, 2004 at 10:37 AM.
"The wise man doesn't know all the answers, but he knows where to find them."
VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15
-
Apr 26th, 2004, 01:52 PM
#2
I would just delete all the unwanted sheets out of the workbook then save it; it will have only the one sheet in it.
If you need to keep those other sheets, then obviouosly create a new workbook for the sheet you want to keep, setting the workbook to open with only 1 sheet in it.
That help?
Dave
-
Apr 26th, 2004, 06:23 PM
#3
Lively Member
Here are three simple lines of code that go a long way in what you are trying to accomplish.
Here is what I use.
VB Code:
Sub movesheet2newbook()
Application.ScreenUpdating = False
Sheets("test").Copy
ActiveWorkbook.Close savechanges:=True, FileName:="C:\onesheet.xls"
End Sub
Works since the default action of copying sheets is to paste to a new workbook. It will only send what you require. Then we close out of the new workbook and with screen updating set to false, no one is the wiser that you just moved from one book to the other, and you finish in your original book.
For multiple sheets, use the following:
VB Code:
Sub movesheets2newbook()
Application.ScreenUpdating = False
Sheets(Array(Sheetname, Sheetname2, Sheetname3)).Copy
ActiveWorkbook.Close savechanges:=True, FileName:="C:\threesheets.xls"
End Sub
-----
#VBA, VB 6 Professional Edition, Office XP Developper. Excel 97, Excel 2000, Excel XP
I miss my VIC 20.
Never should have upgraded to my commodore 64. ...
-
Apr 26th, 2004, 10:12 PM
#4
Addicted Member
Yep.
BTW: An easy way to figure out stuff like this is to start recording a macro and then do the action (right click on "mysheet", and select "new workbook" with "copy" checked).
On a lot of stuff I do, sometimes it's just easier to record a macro to get a code section instead of typing it out by hand.
-
Apr 27th, 2004, 10:37 AM
#5
Thread Starter
Hyperactive Member
Thanks all.
What I wound up doing was creating a new workbook (WorkBooks ... .Add)
copying the sheet there, doing the sheet processing, then saving the new temp workbook.
The original workbook & sheet has to remain unchanged.
Thanks, DaveBo
"The wise man doesn't know all the answers, but he knows where to find them."
VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|