Split Excel workbooks in same excel

N

nittin123goel

New Member
Joined
May 24, 2011
Messages
4
Hey Hi

I have one more problem I have a large excel sheet with different sheets in it.
There almost 90 files with 10 -15 sheets in each file. How can I split them.

I was trying this code .. But it was not working

Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
Next W
End Sub

It doesn't split the file , just save it to same path with sheet name..

Thanks in advance
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Maybe:
Code: 
Sub SplitSheets()
'warning: this will automatically overwrite existing files
 
Dim W As Worksheet, WB As Workbook, WPath As String
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
WPath = ActiveWorkbook.Path
 
For Each W In Worksheets
Set WB = Workbooks.Add
W.Copy WB.Sheets(1)
For i = WB.Sheets.Count To 2 Step -1
WB.Sheets(i).Delete
Next
WB.SaveAs WPath & "/" & W.Name
WB.Close
Next W
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
End Sub
This code will automatically overwrite existing files in your folder so use with care.
 
Upvote 0
Your post is not clear.

"I have a large excel sheet with different sheets in it."

Maybe you mean you have a large workbook with different sheets in it.

I say maybe, because then you are on about 90 files with 10 or 15 sheets yet your attempted code is only looking at one singular workbook.

It will be a good start if you define what "split" means to you, what you are actually working with, and what yoru expected results are.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Splitting worksheets in Excel with Senstivity Labels following master file using VBA
Replies
5
Views
302
NdNoviceHlp
NdNoviceHlp
H
  • Solved
Splitting Data into Workbooks - EDIT HELP
Replies
1
Views
305
horrellbt01
H
J
  • Question Question
Split worksheets into separate excels AND only save those cells with data in them.
Replies
4
Views
340
mumps
mumps
I
  • Question Question
How to Set Excel's "Find All" Feature to Search Entire Workbook by Default rather than sheet only ?
Replies
1
Views
195
RoryA
RoryA
J
  • Solved
VBA script - split sheets into unique folder named of cell entry
Replies
7
Views
522
james00
J

Forum statistics

Threads
1,225,157
Messages
6,183,249
Members
453,152
Latest member
ChrisMd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top