# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] save active sheet

## VilMarci

Hi,

How can I save only the active sheet of a workbook in VBA?

Marci

----------


## William

Sub test()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\Test.xls"
ActiveWorkbook.Close
End Sub

What did you want to happen to the original workbook?
--


XL2003
Regards

William
willwest22@yahoo.com


"VilMarci" <dontsend@here.com> wrote in message
news:%2303MpFsWFHA.3140@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> How can I save only the active sheet of a workbook in VBA?
>
> Marci
>
>

----------


## VilMarci

Hi,

Thanks for the quick reply.
The main task would be to separate the workbook into separate files per
sheet.

Marton


"William" <willwest22@yahoo.com> wrote in message
news:Opq4QQsWFHA.3760@TK2MSFTNGP15.phx.gbl...
>
> Sub test()
> ActiveSheet.Copy
> ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\Test.xls"
> ActiveWorkbook.Close
> End Sub
>
> What did you want to happen to the original workbook?
> --
>
>
> XL2003
> Regards
>
> William
> willwest22@yahoo.com
>
>
> "VilMarci" <dontsend@here.com> wrote in message
> news:%2303MpFsWFHA.3140@TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > How can I save only the active sheet of a workbook in VBA?
> >
> > Marci
> >
> >
>
>

----------


## Mountain_Dewski

This should save them in seperate workbooks...

Sub test()

Dim iNoSheets As Integer
Dim i As Integer

iNoSheets = Worksheets.Count

For i = 1 To iNoSheets
    Worksheets(i).Activate
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:="C:\Test" & i & ".xls"
    ActiveWorkbook.Close
Next

End Sub

----------


## Andibevan

Hi,

The code provided will do that - the activesheet.copy command is not like
copy and paste.  It creates a copy of the active sheet, in a new workbook.
The new workbook is then saved, thus saving your sheet.

Andi

"VilMarci" <dontsend@here.com> wrote in message
news:OMpHj%23sWFHA.2128@TK2MSFTNGP14.phx.gbl...
Hi,

Thanks for the quick reply.
The main task would be to separate the workbook into separate files per
sheet.

Marton


"William" <willwest22@yahoo.com> wrote in message
news:Opq4QQsWFHA.3760@TK2MSFTNGP15.phx.gbl...
>
> Sub test()
> ActiveSheet.Copy
> ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\Test.xls"
> ActiveWorkbook.Close
> End Sub
>
> What did you want to happen to the original workbook?
> --
>
>
> XL2003
> Regards
>
> William
> willwest22@yahoo.com
>
>
> "VilMarci" <dontsend@here.com> wrote in message
> news:%2303MpFsWFHA.3140@TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > How can I save only the active sheet of a workbook in VBA?
> >
> > Marci
> >
> >
>
>

----------


## Tom Ogilvy

Sub test()
Dim sh as Worksheet
for each sh in Worksheets
sh.Copy
Application.displayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\" & _
sh.Name & ".xls"
Application.displayAlerts = True
ActiveWorkbook.Close Savechanges:=False
Next
End Sub

--
Regards,
Tom Ogilvy

"VilMarci" <dontsend@here.com> wrote in message
news:OMpHj%23sWFHA.2128@TK2MSFTNGP14.phx.gbl...
> Hi,
>
> Thanks for the quick reply.
> The main task would be to separate the workbook into separate files per
> sheet.
>
> Marton
>
>
> "William" <willwest22@yahoo.com> wrote in message
> news:Opq4QQsWFHA.3760@TK2MSFTNGP15.phx.gbl...
> >
> > Sub test()
> > ActiveSheet.Copy
> > ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\Test.xls"
> > ActiveWorkbook.Close
> > End Sub
> >
> > What did you want to happen to the original workbook?
> > --
> >
> >
> > XL2003
> > Regards
> >
> > William
> > willwest22@yahoo.com
> >
> >
> > "VilMarci" <dontsend@here.com> wrote in message
> > news:%2303MpFsWFHA.3140@TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > >
> > > How can I save only the active sheet of a workbook in VBA?
> > >
> > > Marci
> > >
> > >
> >
> >
>
>

----------


## VilMarci

Thanks for you all!

This .copy I didn't know.

I have a lot of linked cells. Is it possible to use and save the current
static values, so the persons who receives the standalone sheets will have
all the data they need?

Marton

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:Oiox2MtWFHA.1152@tk2msftngp13.phx.gbl...
> Sub test()
> Dim sh as Worksheet
> for each sh in Worksheets
> sh.Copy
> Application.displayAlerts = False
> ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\" & _
>      sh.Name & ".xls"
> Application.displayAlerts = True
> ActiveWorkbook.Close Savechanges:=False
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "VilMarci" <dontsend@here.com> wrote in message
> news:OMpHj%23sWFHA.2128@TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > Thanks for the quick reply.
> > The main task would be to separate the workbook into separate files per
> > sheet.
> >
> > Marton
> >
> >
> > "William" <willwest22@yahoo.com> wrote in message
> > news:Opq4QQsWFHA.3760@TK2MSFTNGP15.phx.gbl...
> > >
> > > Sub test()
> > > ActiveSheet.Copy
> > > ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\Test.xls"
> > > ActiveWorkbook.Close
> > > End Sub
> > >
> > > What did you want to happen to the original workbook?
> > > --
> > >
> > >
> > > XL2003
> > > Regards
> > >
> > > William
> > > willwest22@yahoo.com
> > >
> > >
> > > "VilMarci" <dontsend@here.com> wrote in message
> > > news:%2303MpFsWFHA.3140@TK2MSFTNGP14.phx.gbl...
> > > > Hi,
> > > >
> > > > How can I save only the active sheet of a workbook in VBA?
> > > >
> > > > Marci
> > > >
> > > >
> > >
> > >
> >
> >
>
>

----------


## VilMarci

Solved.

Thank you all again!

Marton

"VilMarci" <dontsend@here.com> wrote in message
news:%23y2HrdtWFHA.2128@TK2MSFTNGP14.phx.gbl...
> Thanks for you all!
>
> This .copy I didn't know.
>
> I have a lot of linked cells. Is it possible to use and save the current
> static values, so the persons who receives the standalone sheets will have
> all the data they need?
>
> Marton
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:Oiox2MtWFHA.1152@tk2msftngp13.phx.gbl...
> > Sub test()
> > Dim sh as Worksheet
> > for each sh in Worksheets
> > sh.Copy
> > Application.displayAlerts = False
> > ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\" & _
> >      sh.Name & ".xls"
> > Application.displayAlerts = True
> > ActiveWorkbook.Close Savechanges:=False
> > Next
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "VilMarci" <dontsend@here.com> wrote in message
> > news:OMpHj%23sWFHA.2128@TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > >
> > > Thanks for the quick reply.
> > > The main task would be to separate the workbook into separate files
per
> > > sheet.
> > >
> > > Marton
> > >
> > >
> > > "William" <willwest22@yahoo.com> wrote in message
> > > news:Opq4QQsWFHA.3760@TK2MSFTNGP15.phx.gbl...
> > > >
> > > > Sub test()
> > > > ActiveSheet.Copy
> > > > ActiveWorkbook.SaveAs Filename:="C:\MyFolder\MySubFolder\Test.xls"
> > > > ActiveWorkbook.Close
> > > > End Sub
> > > >
> > > > What did you want to happen to the original workbook?
> > > > --
> > > >
> > > >
> > > > XL2003
> > > > Regards
> > > >
> > > > William
> > > > willwest22@yahoo.com
> > > >
> > > >
> > > > "VilMarci" <dontsend@here.com> wrote in message
> > > > news:%2303MpFsWFHA.3140@TK2MSFTNGP14.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > How can I save only the active sheet of a workbook in VBA?
> > > > >
> > > > > Marci
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

----------


## EMoe

Hello,

With this code, how do I call an input box so that the user can enter a file name (e.g. todays date), then save the workbook. Or maybe let the macro grab the date from the sheet and save it as the file name.

Sub test()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\ArchiveFolder\Test.xls"
ActiveWorkbook.Close
End Sub

Thanks,
EMoe

----------


## McNulty

Just a bump for this to say thanks. Helped me out with one I was writing today!

----------


## nigelbloomy

Just found this in a Google search and though I would add in the method to let a user select where to save a file as well as to only put values and formats into the new file.
I know everyone says to use CutCopyMose to get rid of the selection lines.  That works fine and it does clear the memory of all the data selected so you don't get an error message that there is a lot of data on the clipboard when you go to save.  I also added in Range("A1").select because otherwise when you open the new file the whole usedrange is selected.  It's a small thing, but I get annoyed when I open a file and huge areas are already selected.  




```
Please Login or Register  to view this content.
```

----------


## tudor123

hi everybody , 

I am trying to save a worksheet as a new file , but for some reason I get upon an error . 



```
Please Login or Register  to view this content.
```


This is the code that I use 
The V1 cell looks like : C:\Users\Dell\Desktop\Richeste\Richieste no103 - 5/5/2018mat_prod.xls it has this formula : ="C:\Users\Dell\Desktop\Richeste\Richieste no"&E5&"mat_prod.xls"

If I use a simple name - it works , but if I use this one it gives me an error : Capture.PNG


Any ideea what am i doing wrong ? 

Thanks a lot 
Tudor

----------


## FDibbins

Tudor, welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------

