[Excel VBA] Excel macro example that saves a workbook for each sheet

Windows

Hello!This time, I will try to save multiple sheets in one book to different books with Excel macro.

Excel macro is a very convenient function, but it is quite a hurdle to make it yourself.
However, once it is made, it can be reused many times, which greatly improves work efficiency.

In this case, I thought that there are quite a few cases where I want to make the Excel ledger, which manages sheets by month, into separate books, so I made a macro with that as the theme.

If you use this macro, you can save it easily and speedily.

* Currently, it is for people with macro experience.I would like to summarize how to register macros later.

Thing you want to do

I want to divide the original workbook by the number of sheets

-I want to save one Excel file consisting of multiple sheets for each sheet.

-Since I want to execute the macro and save it, save the book with the sheet name as the file name.

・ I want to save all of them separately, not just one.

Script example

Sub SplitSheets()
 
Dim i As Integer
Dim wb1, wb2 As Workbook
Dim Cnt As Integer
 
'1)シート数の取得
Set wb1 = ActiveWorkbook
Cnt = wb1.Sheets.Count

Dim CopyFileName

'EX)処理が止まらないようにダイヤログを抑制
Application.DisplayAlerts = False

For i = 1 To Cnt

'2)シート名でブックを作成
CopyFileName = ThisWorkbook.Path & "\" & wb1.Worksheets(i).Name & ".xlsx"
Workbooks.Add.SaveAs Filename:=CopyFileName

'3)元のブックのシートを先ほど作ったシート名のブックにコピー
Set wb2 = Workbooks(wb1.Worksheets(i).Name & ".xlsx")

wb1.Worksheets(i).Copy before:=wb2.Worksheets(1)
wb2.Worksheets(1).Name = wb1.Worksheets(i).Name
wb2.Worksheets("Sheet1").Delete
wb2.Close savechanges:=True

Next i

'EX)抑制したダイヤログ設定もとに戻す 
Application.DisplayAlerts = True

End Sub

If you register this macro in the Excel macro you want to save separately and execute it, the file will be divided by the sheet name all the way to the location of the Excel file.

Commentary

I would like to explain the main parts by excerpting them.

XNUMX) Acquisition of the number of sheets

First, get the number of sheets in the book that is currently open.
The number of sheets can be obtained from the workbook object property "sheets.count".

Set wb1 = ActiveWorkbook Create an object with the currently open workbook as wb1 Cnt = wb1.Sheets.Count Call the Sheets.Count property to get the number of sheets

This tells you how many times you have to process.

XNUMX) Create a book with the sheet name

You can save it as a separate workbook by using the "Workbooks.Add.SaveAs" method.It becomes a function of saving as.
* Since the new book is created at this time, it is a book with only "Sheet1".

It's about saving as a name, so you need the name you want to give.Since we want the original sheet name here, we get it with the Name property of the Worksheets object.

ThisWorkbook.Path & "\" & wb1.Worksheets (i) .Name & ".xlsx" ThisWorkbook.Path Get the location of the currently open workbook wb1.Worksheets (i) .Name Get the i-th sheet name

This time, I'm using "ThisWorkbook.Path" because I want to save it in the same location as the original workbook.
Therefore, please note that if the book name and sheet name are the same, it will fail.

Since the process of repeating the number of sheets in the immediately preceding For minute is described, the process is performed while adding "i" one by one from XNUMX to the maximum number of sheets.At this time, "i" serves as the index number of the sheet.

Excel sheets are managed by index numbers, and you can specify sheets by number, such as the first page, second page, and so on.

Therefore, when "i" is XNUMX, you can get ".Name" of the first sheet.
Since this is repeated for all sheets, a book is created for each sheet.

XNUMX) Copy the sheet of the original book to the book with the sheet name created earlier

Next, copy the sheet to the created book.
First, get the created book to "wb2" so that it is easy to handle.The principle is the same as before, using the index number.

Set wb2 = Workbooks (wb1.Worksheets (i) .Name & ".xlsx")

Next, I will copy the sheet. "Wb1" is the original book and "wb2" is the copy destination book.

wb1.Worksheets (i) .Copy before: = wb2.Worksheets (1)

It specifies that the copy destination workbook should be copied before the first sheet.
In this case, add the "before:" option to ".copy".At this time, "Worksheets (1)" is in front of the leftmost sheet in the index number, so it is inevitably copied to the beginning.

wb2.Worksheets ("Sheet1"). Delete wb2.Close savechanges: = True

Finally, delete the original sheet with the name "Sheet1", save it, and you're done.

After this, increase "i" by XNUMX and repeat until the last sheet.
This will save all the sheets in a separate workbook.

EX) Suppresses dialog so that processing does not stop

"Application.DisplayAlerts" is a setting value that suppresses dialog display such as warnings.

If this remains "True", a warning will be displayed every time you save one page.
Then you will have to click every time, so this time I set it to "False" because I want it to be done automatically until the end.

Afterword

This time, I introduced a macro that saves an Excel book composed of multiple sheets separately for each sheet.

There are books that are divided into sheets in various situations, such as ledgers that were managed by month and check sheets that are divided into sheets by model.If you didn't make it yourself, it's hard to disassemble it.

When I work as an SE, there are many cases where I do this work unexpectedly.Moreover, it is quite troublesome to do it manually.
The management ledger is usually this case, and for some reason I have to give a pinpoint sheet to the customer.
After all, it is best to let macros do this kind of repetitive work.

It was a rough explanation, so it may have been difficult to understand ...
If you have any questions, please comment ♪

I hope it helps you.

Comment

Translate »
I copied the title and URL