LinkBack URL
About LinkBacksHi,
How can I save only the active sheet of a workbook in VBA?
Marci
Hi,
How can I save only the active sheet of a workbook in VBA?
Marci
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
>
>
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
> >
> >
>
>
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
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
> >
> >
>
>
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
> > >
> > >
> >
> >
>
>
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
> > > >
> > > >
> > >
> > >
> >
> >
>
>
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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
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
Just a bump for this to say thanks. Helped me out with one I was writing today!
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.
Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.
<---Click * Add Reputation for all helpful comments. It's like giving a smile.
Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.
hi everybody ,
I am trying to save a worksheet as a new file , but for some reason I get upon an error .
This is the code that I usePlease Login or Register to view this content.
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
Tudor, welcome to the forum
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.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks