Splitting worksheets into Files 1

[scottie1]

Mornin Everybody

Just a quick question, How do you split worksheets within a workbook all in one click of a macro and then save them as individual file's with the sheet name as the file names?

cheers

Adam

 

[xlbo]

Basic way

sub Splitter()
dim stdPath as string
stdPath = "Base path for where you want to save the files\"
application.screenupdating = false
For each wks in thisworkbook.worksheets
    wks.copy
    activeworkbook.saveas stdPath & wks.name & ".xls"
    activeworkbook.close
next
end sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[scottie1]

Cheers Geoff

Thats spot on you
would think that would be a standard tool

 

[scottie1]

Hi Xlbo

I don't suppose you know how what I need to do to the code to get it to run from any workbook.

 

[xlbo]

Put it in your personal.xls and change THISworkbook to ACTIVEworkbook

It will then run on whatever is the active workbook at the time

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[scottie1]

Cheers Xlbo

I thought it was that part of the code. I am slowly learning.

 

[xlbo]

THISworkbook will always refer to the workbook where the code is housed. It's very useful when you are opening and closing files all over the place and you need to refer to your main file

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question