Seperating worksheets

I have a workbook with about 15 worksheets in it. Is there a quick way to 
automatically seperate all 15 into single workbooks. or do I have to go 1 by 
1?

Thanks, 
Steve
0
Steve3367 (862)
10/19/2004 2:57:08 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
560 Views

Similar Articles

[PageSpeed] 32

Hi Steve

Try this macro
It will save each sheet in a seperte wornook in C:\

Sub test()
    Dim a As Integer
    Dim wb As Workbook
    Application.ScreenUpdating = False
    For a = 1 To ThisWorkbook.Worksheets.Count
        ThisWorkbook.Sheets(a).Copy
        Set wb = ActiveWorkbook
        wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
        wb.Close False
        Set wb = Nothing
    Next a
    Application.ScreenUpdating = True
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" <Steve@discussions.microsoft.com> wrote in message news:C4FA85BB-7BDA-42E9-9D5C-CE85C120084D@microsoft.com...
>I have a workbook with about 15 worksheets in it. Is there a quick way to
> automatically seperate all 15 into single workbooks. or do I have to go 1 by
> 1?
>
> Thanks,
> Steve 


0
rondebruin (3790)
10/19/2004 3:10:02 PM
Oops

My english is bad<g>

>seperte wornook

Separate workbook



-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:uyKB42etEHA.2800@TK2MSFTNGP10.phx.gbl...
> Hi Steve
>
> Try this macro
> It will save each sheet in a seperte wornook in C:\
>
> Sub test()
>    Dim a As Integer
>    Dim wb As Workbook
>    Application.ScreenUpdating = False
>    For a = 1 To ThisWorkbook.Worksheets.Count
>        ThisWorkbook.Sheets(a).Copy
>        Set wb = ActiveWorkbook
>        wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
>        wb.Close False
>        Set wb = Nothing
>    Next a
>    Application.ScreenUpdating = True
> End Sub
>
>
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Steve" <Steve@discussions.microsoft.com> wrote in message news:C4FA85BB-7BDA-42E9-9D5C-CE85C120084D@microsoft.com...
>>I have a workbook with about 15 worksheets in it. Is there a quick way to
>> automatically seperate all 15 into single workbooks. or do I have to go 1 by
>> 1?
>>
>> Thanks,
>> Steve
>
> 


0
rondebruin (3790)
10/19/2004 3:13:54 PM
The problem i am running into is that i can't find it. I am on a network here 
at work. I swapped the c:/ with the path where i want the file to be saved. 
We do not have a c drive. Shouldn't have worked? I can't find the seperated 
documents anywhere. 

"Ron de Bruin" wrote:

> Oops
> 
> My english is bad<g>
> 
> >seperte wornook
> 
> Separate workbook
> 
> 
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:uyKB42etEHA.2800@TK2MSFTNGP10.phx.gbl...
> > Hi Steve
> >
> > Try this macro
> > It will save each sheet in a seperte wornook in C:\
> >
> > Sub test()
> >    Dim a As Integer
> >    Dim wb As Workbook
> >    Application.ScreenUpdating = False
> >    For a = 1 To ThisWorkbook.Worksheets.Count
> >        ThisWorkbook.Sheets(a).Copy
> >        Set wb = ActiveWorkbook
> >        wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
> >        wb.Close False
> >        Set wb = Nothing
> >    Next a
> >    Application.ScreenUpdating = True
> > End Sub
> >
> >
> > -- 
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> > "Steve" <Steve@discussions.microsoft.com> wrote in message news:C4FA85BB-7BDA-42E9-9D5C-CE85C120084D@microsoft.com...
> >>I have a workbook with about 15 worksheets in it. Is there a quick way to
> >> automatically seperate all 15 into single workbooks. or do I have to go 1 by
> >> 1?
> >>
> >> Thanks,
> >> Steve
> >
> > 
> 
> 
> 
0
Steve3367 (862)
10/19/2004 3:43:01 PM
I have test in on my home network with

wb.SaveAs "\\Jelle\SharedDocs\" & wb.Sheets(1).Name & ".xls"

and it is working for me

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve" <Steve@discussions.microsoft.com> wrote in message news:C16A4398-5323-4EB7-ADD9-05E1D77A1448@microsoft.com...
> The problem i am running into is that i can't find it. I am on a network here
> at work. I swapped the c:/ with the path where i want the file to be saved.
> We do not have a c drive. Shouldn't have worked? I can't find the seperated
> documents anywhere.
>
> "Ron de Bruin" wrote:
>
>> Oops
>>
>> My english is bad<g>
>>
>> >seperte wornook
>>
>> Separate workbook
>>
>>
>>
>> -- 
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:uyKB42etEHA.2800@TK2MSFTNGP10.phx.gbl...
>> > Hi Steve
>> >
>> > Try this macro
>> > It will save each sheet in a seperte wornook in C:\
>> >
>> > Sub test()
>> >    Dim a As Integer
>> >    Dim wb As Workbook
>> >    Application.ScreenUpdating = False
>> >    For a = 1 To ThisWorkbook.Worksheets.Count
>> >        ThisWorkbook.Sheets(a).Copy
>> >        Set wb = ActiveWorkbook
>> >        wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
>> >        wb.Close False
>> >        Set wb = Nothing
>> >    Next a
>> >    Application.ScreenUpdating = True
>> > End Sub
>> >
>> >
>> > -- 
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> > "Steve" <Steve@discussions.microsoft.com> wrote in message news:C4FA85BB-7BDA-42E9-9D5C-CE85C120084D@microsoft.com...
>> >>I have a workbook with about 15 worksheets in it. Is there a quick way to
>> >> automatically seperate all 15 into single workbooks. or do I have to go 1 by
>> >> 1?
>> >>
>> >> Thanks,
>> >> Steve
>> >
>> >
>>
>>
>> 


0
rondebruin (3790)
10/19/2004 4:27:48 PM
The macro functions correctly for me as well.  Thanks, Ron!

"Ron de Bruin" wrote:

> I have test in on my home network with
> 
> wb.SaveAs "\\Jelle\SharedDocs\" & wb.Sheets(1).Name & ".xls"
> 
> and it is working for me
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "Steve" <Steve@discussions.microsoft.com> wrote in message news:C16A4398-5323-4EB7-ADD9-05E1D77A1448@microsoft.com...
> > The problem i am running into is that i can't find it. I am on a network here
> > at work. I swapped the c:/ with the path where i want the file to be saved.
> > We do not have a c drive. Shouldn't have worked? I can't find the seperated
> > documents anywhere.
> >
> > "Ron de Bruin" wrote:
> >
> >> Oops
> >>
> >> My english is bad<g>
> >>
> >> >seperte wornook
> >>
> >> Separate workbook
> >>
> >>
> >>
> >> -- 
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:uyKB42etEHA.2800@TK2MSFTNGP10.phx.gbl...
> >> > Hi Steve
> >> >
> >> > Try this macro
> >> > It will save each sheet in a seperte wornook in C:\
> >> >
> >> > Sub test()
> >> >    Dim a As Integer
> >> >    Dim wb As Workbook
> >> >    Application.ScreenUpdating = False
> >> >    For a = 1 To ThisWorkbook.Worksheets.Count
> >> >        ThisWorkbook.Sheets(a).Copy
> >> >        Set wb = ActiveWorkbook
> >> >        wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
> >> >        wb.Close False
> >> >        Set wb = Nothing
> >> >    Next a
> >> >    Application.ScreenUpdating = True
> >> > End Sub
> >> >
> >> >
> >> > -- 
> >> > Regards Ron de Bruin
> >> > http://www.rondebruin.nl
> >> >
> >> >
> >> > "Steve" <Steve@discussions.microsoft.com> wrote in message news:C4FA85BB-7BDA-42E9-9D5C-CE85C120084D@microsoft.com...
> >> >>I have a workbook with about 15 worksheets in it. Is there a quick way to
> >> >> automatically seperate all 15 into single workbooks. or do I have to go 1 by
> >> >> 1?
> >> >>
> >> >> Thanks,
> >> >> Steve
> >> >
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Naidu (2)
3/8/2005 1:21:02 AM
Reply:

Similar Artilces:

How do I limit the number of rows displayed on a worksheet?
I have a file with about 400 rows of data, however 50,000 rows are displayed. So it is difficult to use the scroll bars, as my data is a very small portion of the entire workbook. How do I limit the display to show and scroll only through my 400 rows? Select the rows you do not want to see and hide them. -- Gary''s Student "cms31" wrote: > I have a file with about 400 rows of data, however 50,000 rows are displayed. > So it is difficult to use the scroll bars, as my data is a very small > portion of the entire workbook. How do I limit the display to sho...

Allowing different users access to different worksheets
I am setting up a shared document to which 27 different users need access. Each user will have their own tab which they can edit. I would like to restrict what each user can see. The content is not top-secret so it doesn't matter if it's easy to crack. In an ideal situation I'd like that when the user opens the file that it asks for a password. The password would open access to whatever tab the password applied to. There would have to be an overall password which allows visibility to all. I'd prefer if users could only see their own sheet. If it makes a differenc...

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

worksheets 02-27-10
sheet1 is a summary page. sheets two thru 13 represent the months.on sheet 1 column 5 row 4 i've entered"=sheet2!r26. i would like to have that info placed in the same row but different columns BUT I WANT column 6(the adjacent column to read"=sheet3!r26". the next adj column i want to read "=sheet4!r26". i would like this pattern to continue thru column 13. do i have to do this manually? -- thanx jmh Hi, You can try something like: =INDIRECT("Sheet"&COLUMN()&"!$R$26") This assumes your first column is B (2) If it is s...

Conditional Formatting across different worksheets 04-27-10
Hello everyone, I looked over the internet and in this forum, but I have not found a solution to my problem (perhaps I did not understand the solutions that were presented on the Internet). Anyway, this is the problem: I have two sheets, one that I use as a control (i.e. a back up) that I will call Sheet 2, while Sheet 1 is a duplicate of Sheet 2 that is sent out to different people for quarterly updates. I keep Sheet2 hidden in the workbook I send out for updates so that only Sheet1 is visible. What I want to do is make the cells in Sheet 1 automatically change color if they ...

HELP! Worksheet Problem
Hi I have a workbook containing 6 worksheets being Nov 01, Feb 02, May 02, Aug 02, Nov 02 and Jan 03. Each worksheet contains details of loans on these dates to clients. Each client has a unique ID number and is in its own row with columns A-I being ID, Prefix, First Name through to post code etc and column J being the loan amount. The first worksheet (Nov 01) contains 76 clients, the second (Feb 02) contains 189, (May 02) = 297, Aug 02 = 345, Nov 02 = 342 and the last (Jan 03) contains 350 clients. I need a way to import all the details of the clients from each worksheet to a ...

If a worksheet name is = to test then a msgbox appears
I'm looking for a macro that will display a msgbox if a worksheet is = to test. For example, if the name of a sheet in a workbook is equal to test then display msgbox saying sheet already exists. Thanks Vick dim ws as worksheet set ws = nothing on error resume next set ws = worksheets("test") on error goto 0 if ws is nothing then msgbox "doesn't exist" else msgbox "already exists" end if Vick wrote: > > I'm looking for a macro that will display a msgbox if a worksheet is = to test. > > For example, if the name of a sheet in a w...

Seperate Recipient Group
I want to set up a group of users in E2K3. The goal is to have them in a separate storage group with a separate Global Address List (although they can have permission to view the main GAL). Any tips on the best way to set this up? Thanks, Mitchel ...

Multipule Worksheets
How do you set Excel 2002 to open all spreadsheets into one document instead of having multipule documents open up across your toolbar? ...

Numbering of worksheets
I have a workbook with a number of sheets all individually named. I am trying to loop through most of them in VBA by using something along the lines of:- for a=5 to 16 sheet(a).select rest of code Next My problem stems from the fact that I have no idea how the sheet numbering system used in VBA relates to the sheet numbers in the workbook or the order they are displayed in the workbook. I have three or four sheets that I wish to exclude as the information required is collated on these sheets and I am constantly adding and removing sheets as projects are completed. It would probably suit ...

Convert Excel Worksheets to jpg, png and Tiff images?
Website: http://www.joyprinter.com Joy Image Printer is a high-performance virtual printer which enables you to convert any printable document into standard BMP, GIF, JPEG, PNG and TIFF image, and keep the exact look and feel of the original document. Since the converted image has the complete information from the original file without altering characters or fonts, you don't need to install the original software to view the document. Key Features: (1) Installs as a standard Windows printer, works for all Windows applications directly (2) Converts any printable documents like doc, xls, p...

Macro to add worksheet
hi,all I have a document that is 1 columns. I want add worksheets for example: A1 sheet1 sheet1 sheet1 sheet8 ----->VBA add sheet sheet8 sheet8 sheet3 sheet3 ...... ...... sheet4 sheet4 This little macro will run down the column and add a worksheet for each entry that does not already have a worksheet: Sub SheetAdder() Set baseSheet = ActiveSheet n = Cells(Rows.Count, "A").End(xlUp).Row For i =...

Worksheet Specific Range Name
How do I define a range name on a worksheet that is specific to that worksheet? i.e. I want to use the same range name on several worksheets. Regards, On Oct 19, 10:53 am, Alan1 <alan.do...@virgin.net> wrote: > How do I define a range name on a worksheet that is specific to that > worksheet? i.e. I want to use the same range name on several > worksheets. > > Regards, 1 Select the range of cells that you want to name. 2 Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box. 3 In the Names in Workbook box, enter the name of the c...

moving worksheets
How can I move a worksheet within the main excel window,when the worksheet header has become hidden behind the toolbar. You could resize the window: Window|Arrange|tiled To see all the tabs and titlebars then rearrange each by manually. steve budd wrote: > > How can I move a worksheet within the main excel > window,when the worksheet header has become hidden behind > the toolbar. -- Dave Peterson ec35720@msn.com Click Window,Arrange which should treat all open windows the same (tiled or cascaded) The hidden header will then become visible. "steve budd" <ano...

Copying a formula with multiple worksheets down rows and across co
I have the following formula:=IF('LC + CC'!B3="","",'LC + CC'!B3+'LC + CC'!B3*F69+'Sales Price (2)'!B3*'Sales Price'!F70+'Sales Price (2)'!B3*'Sales Price'!F71+'Sales Price (2)'!B3*'Land Cost'!B14) Im trying to copy this down multiple rows as follows:=IF('LC + CC'!B4="","",'LC + CC'!B4+'LC + CC'!B4*F69+'Sales Price (2)'!B4*'Sales Price'!F70+'Sales Price (2)'!B4*'Sales Price'!F71+'Sales Price (2)'!B4*'Land ...

Too Many Cursors In Excel Worksheet
How do I get rid of extra cursors in my Excel worksheet? When I click on one cell location, I have a cursor; when I click on another cell location, I have another cursor but the first cursor remains on the work sheet Josey If by "cursors" you mean selected cells......... Do you see "Add" down on the Status Bar? Press F8 twice or SHIFT + F8 once to get out of this mode. Gord Dibben Excel MVP On Mon, 18 Oct 2004 16:47:01 -0700, "Josey" <Josey@discussions.microsoft.com> wrote: >How do I get rid of extra cursors in my Excel worksheet? When I click ...

Lock a worksheet
I need to lock a worksheet after 1 month has elapsed. Any idea on how i can do this please? Please refrain from multiposting. If you need to post to multiple groups, cross post instead.. As I said in the functions group, trust your customers. There is very little you can do if someone is determined to use a spreadsheet. Copy and paste to another workbook for instance after disabling macros.. -- Regards, Peo Sjoblom "Harry Archer" <ha092003@removehotmail.com> wrote in message news:O9JW6WpsDHA.2304@tk2msftngp13.phx.gbl... > I need to lock a worksheet after 1 month h...

How Do I Protect A Worksheet But Allow Copying Columns
I need to protect a worksheet where I do scenario analysis. Each column is a single scenario where some rows have inputs but most are protected. The trick is I need to be able to copy a column and insert it next to or somewhere on the worksheet while leaving the worksheet and the cells I want in the column that is being copied protected? Do I have to use VB to do this or is there another simpler way? If I have to use VB -- suggestion on how to do this easily are appreciated. Thanks. I would protect the worksheet, then provide a macro that did: unprotect, copy, paste, reprotect If y...

Change Font colour mid-worksheet: Excel 2003
I have a large worksheet (6,000+ rows) that I am continuously updating I need to quickly and easily see all "recent" changes made as I scroll through At present, the data in each worksheet cell are all in black (+ Times Roman) fonts - I would like all changes and additions to be very apparent - in say red - and this should ideally include changing data within a cell - say changing "Note" to "Notes" with just the "s" showing up red Then when I am happy with certain changes, I want to Format Paint them to black as I scroll through - leaving only the qu...

Extended Worksheet data entry form -Debra Dalgleish/Dave Peterson pls help
Hi Dave, I came across your worksheet data entry form, adapted the idea for my project and it worked perfectly fine. Right now, i received a feedback from my team asking whether an edit feature can be incorporated to this worksheet.I suggested them to do the editing direct to the cell but they were hoping there will be a macro solution. Is there a way that we can call back the entire row (the one that needs to be edited) of data in parts data sheet, have them appear in a new sheet and make the changes there. Plus there is an additional criteria whereby we only want to allow them to change 2 ...

Regeneration Of 401 Worksheet
I have a HQ system running with a number of stores. When i generate a 401 worksheet and approve it, the stores connect at there scheduled times and process the worksheet succesfully, but no new 401 is regenerated. It is my understanding that as long as a schedule is present and the 401 is successfull, then the next one should be automatic. Can anyone give me any advice or help in where to look, to find out why this is happening? ...

Separate Worksheet Linking
I am using Excel 2003 I have one worksheet that I use to document an incident. It must provide: Name of person Date Account Type of issue Stuff like this - another department keeps a running total of these events on a separate spreadsheet on a common drive. I was wondering if there were a method by which I could link certain cells from my form/worksheet in excel to theirs so that there won't be so much manual input and risk inputting inaccurate information multiple times. Even just pointing me in the right direction would be so helpful. It really is time consuming this way. Sinc...

worksheet question
Using Excel 2003 with Windows XP Home. I edit a monthly guild newsletter with Publisher 2003 and tried to add in the one page size membership roster that I created with Excel. In trying to copy and paste or, thru Publisher, insert it as a worksheet, the formatting was all off, cells too large, page too large, the table looked totally different than in Excel. Is there a formatting step when creating a table that I'm not aware of? I'll be looking for a latest Dummies book, I know I'm not up to speed with the program. Perhaps you can copy the cells, and paste as a picture in...

Worksheet to template
I have a worksheet with data and formulas from 2003. I want to use the same setup for 2004, including formulas, but starting from January 2004 with data I input from December, 2003. How do I clear the 2003 worksheet of values without clearing the formulas, to then save it as a template for each year thereafter? Michael, try edit, goto, special, constants, delete -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my e...

Referencing a Worksheet Name in Formula
Hello, I have an issue I can't seem to find an answer to, which I believe is doable. I have a worksheet named RU (for Rollup) which pulls information from 3 other worksheets named "RCA","RCB", "RCC" through an Index Match formula. I am hoping to insert into the formula the name of the tab I would like the formula to search from. I would like formula to look for the name of the worksheet in a cell within the same row, find the worksheet and then apply the index match formula. That way I can just the name of worksheet it looks from by changing ...