Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

How to extract excel worksheet from a workbook.

sanil (Accountant) (149 Points)

03 October 2018

OnLY EXCEL EXPERT CAN HELP ME

HOW TO EXTRACT DIFFERENT WORKSHEETS FROM A PARTICULAR WORKBOOK .?

I HAVE LOT OF EXCEL SHEETS IN A PARTICUALR WORK BOOK, I WANT SEPERATE WORKBOOK FOR EACH WORKSHEET.IS IT POSSIBLE PLEASE HELP ME.

Reply
Follow
Share

2 Replies

(Guest)

You can use right click on sheet tab below and select move or copy --> To book: new book.

Upgrade to CAclubindia Pro. Subscribe Now!

Satya N Dharani (Finance Professional) (491 Points)
Replied 05 October 2018

dear sanil,

Split a workbook to separate Excel files with VBA code

The following VBA code can help you quickly split multiple worksheets of current workbook to separate Excel files, please do as follows:

1. Create a new folder for the workbook that you want to split, because the split Excel files will be stayed at the same folder as this master workbook.

2. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA: Split a workbook into multiple workbooks and save in the same folder.

Sub Splitbook()

'Updateby20140612

Dim xPath As String

xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False

Application.DisplayAlerts = False

For Each xWs In ThisWorkbook.Sheets

xWs.Copy

Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"

Application.ActiveWorkbook.Close False

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

4. Press the F5 key to run this code. And the workbook is split to separate Excel files in the same folder with the original workbook. See screenshot: