MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Save a single sheet as a separate file

  Asked By: Francisca    Date: Aug 09    Category: MS Office    Views: 1561

I have a workbook with many sheets. These sheets are added based on
the cell value of A1 of "Master" sheet.
My requirement is - Suppose, cell A1 contains "ABC", then the macro
will save the sheet "ABC" as a separate file named "abc" to the same
Suppose, cell A1 of "Master" sheet contains "XYZ", then it will save
the sheet named "XYZ" as a separate file named "xyz" to the same
location and so on. Also the name the sheet shall be "abc,xyz, etc.

Can a macro be provided to :

Extract a copy of a sheet based on cell A1 value of "Master" sheet and
save as a separate file to the location of the main file. The name of
the file should be sheet name



11 Answers Found

Answer #1    Answered By: Mason Evans     Answered On: Aug 09

Try something like this (no error handling):

Option Explicit

Sub Extract_Current_Sheet_to_File()
Dim ws As Object
Dim strName As String
Dim strFN As String
Set ws = ActiveSheet
strName = ws.[a1]
strFN = strName & "xls"

Application.ScreenUpdating = False
ws.Copy Before:=Sheets(1)
ActiveSheet.Name = strName
ActiveWorkbook.SaveAs Filename:=strFN, FileFormat:=xlNormal
Application.ScreenUpdating = True
End Sub

Answer #2    Answered By: Ludwik Fischer     Answered On: Aug 09

code is very good and close, but might I suggest the following
modifications for it to work right:

Sub Extract_Current_Sheet_to_File()

Dim wsToSave As Worksheet

Dim strName As String

On Error Resume Next

strName = ActiveSheet.[a1]

Set wsToSave = Sheets(strName)

If wsToSave Is Nothing Then

MsgBox "There is no sheet  named " & strName & "."

Exit Sub

End If

Application.ScreenUpdating = False

wsToSave.Copy ' copy  this sheet to a new workbook, must use no

' should do some error checking here:

ActiveWorkbook.SaveAs Filename:=strName ' save  the new workbook


'or in do the SaveAs and Close in one statement:

'ActiveWorkbook.Close SaveChanges:=True, Filename:=strName

Application.ScreenUpdating = True

If Dir(strName & ".xls") <> vbNullString Then

MsgBox strName & " was saved successfully"


MsgBox strName & " was NOT saved successfully"

End If

End Sub

Answer #3    Answered By: Rayner Fischer     Answered On: Aug 09

My VBA is horrible and reading these messages has gotten me thinking, would
this work to save  a sheet  that is a series of TRUE/FALSE named  cells with a
couple of named cells that have a number?

I ask because a file  I use has one sheet that helps populate the rest of the
workbook and it is a little hefty at 20MB. If I could save the named cells it
would cut the size down immensely and would require me to transport the saved
file between locations via email.

And if possible would the location  of the named cells matter? I mean would it
screw the VB up if the location of a named cell  was moved elsewhere on the same

Answer #4    Answered By: Adalie Fischer     Answered On: Aug 09

you could use a named  range instead of a named cell.. that way as long as the
name exits the range it equals can change...

Answer #5    Answered By: Ricardo Smith     Answered On: Aug 09

your code exactly working in the desired manner. But
it is saving the file  on the desktop, can this be slightly modified
so that the file will be saved in the same location  where the
original file is saved?

Answer #6    Answered By: Nora Martin     Answered On: Aug 09

Sub Extract_Current_Sheet_to_File()

Dim strName As String

Dim strPath As String

On Error Resume Next

strPath = ActiveWorkbook.Path ' <<<

strName = ActiveSheet.Range("A1").Value

Sheets(strName).Copy ' copy  this sheet  to a new workbook, must use no

ActiveWorkbook.SaveAs Filename:=strPath & "\" & strName


End Sub

Answer #7    Answered By: Kenny Moore     Answered On: Aug 09

exactly working correctly...............

Answer #8    Answered By: Adalgisa Miller     Answered On: Aug 09

Your code is working but has certain problem:

The code is saving the activesheet as a separate  file but my need is not

First I am giving some background:

I have a WB which downloads web data related to stock/shares The stock name
is put in cell  A1 of "Import" sheet. By another macro, I am saving the data
as a separate sheet  in the same WB. Suppose cell A1 contains stock code as
"ABC". Then the code downloads data related to stock "ABC". This data are
saved as a a separate sheet.

Now I change the cell A1 of "Import" sheet to stock code "XYZ" The data of
"xyz" are downloaded and saved as a separate sheet.

Now my requirement is :

I want to run the code from "Import" sheet. The code will save  the sheet,
based on name in cell A1 of "Import" sheet, as a separate file  . Suppose,
cell A1 contains "ABC" it will have to save the "ABC" sheet and not the
"Import" sheet. Your code is saving the "Import" sheet now. The name of the
file should also be "ABC". So that the data of ABC are saved as a separate

Answer #9    Answered By: Girja Garg     Answered On: Aug 09

yes this can be done... easiest would be to record a macro  of move/copy
worksheet and select new workbook  as destination once you have that code
substitute a variable for the worksheet name and you can just pass the value of
cell a1 to the variable when you rund the macro....

Answer #10    Answered By: Tyler Thompson     Answered On: Aug 09

i had some time so i made this.. quick simple... let me know if you need more

Sub copynewbook()
' copynewbook Macro
Dim wbName As String
Dim wsName As String
Dim strSheet As String

wbName = ThisWorkbook.Name
wsName = "Master"
strSheet = Workbooks(wbName).Worksheets(wsName).Range("A1").Value


ActiveWorkbook.SaveAs strSheet & ".xls"
Workbooks(strSheet & ".xls").Close
End Sub

Answer #11    Answered By: Sage Anderson     Answered On: Aug 09

code does not work the way you want.
However, I posted code that does work and so did ohtechie.

The key lines of code are:

strSheetName = ActiveSheet.Range("A1").Value ' get desired sheet  name

Sheets(strSheetName).Copy ' copy  desired sheet into a new

ActiveWorkbook.SaveAs Filename:=strName ' save  the new workbook

ActiveWorkbook.Close ' close the new workbook

Please try this code. (!)

Didn't find what you were looking for? Find more on Save a single sheet as a separate file Or get search suggestion and latest updates.