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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

A
  • Question
VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section
Replies
1
Views
273
John_w
J
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
226
harmless92
H
A
  • Question
Split file based on row and value
Replies
1
Views
145
bobaol
B
D
  • Solved
Export and name sheets within a workbook to a text file in same directory as workbook
Replies
3
Views
554
rlv01
R
Z
  • Question
Excel table To split or not to split??
Replies
5
Views
327
Fluff
Fluff

Forum statistics

Threads
1,216,040
Messages
6,128,454
Members
449,455
Latest member
jesski

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