Combine All Workbooks In Folder Excel VBA Macro

In this post we learn how to combine all the Excel files in a given folder using VBA macros. The macro triggers a dialog box to select a folder from the local drive, and then loops through the workbooks in that folder to copy the contents to the recipient workbook. That includes all the worksheets in each of the workbooks within the target folder. Use the code below to combine all Excel files in a folder into one single workbook.

Protect Selected Workbooks Excel VBA Macro

In this post we learn how to protect all selected workbooks in Excel using VBA macros. The macro protects the workbook structure and the contents of each worksheet with a password. Alternatively, or additionally, another line of code can be added to encrypt the file with that password. The macro initially asks the user to set a password, and then displays a dialog to select the files. The same code can easily be adapted to unprotect the workbooks with a password.

Compare Two Workbooks Excel VBA Macro

In this post we see how to compare two Excel workbooks with VBA macros. The macro checks if the number of worksheets matches, then compares the worksheet names and the contents in the sheets with the same name (this last piece of code has been covered in this other post: Compare Two Worksheets). Whenever there is a mismatch, it highlights the sheet or cells with different values in yellow, and displays a message with cell and worksheet (this could also be copied to a separate sheet instead). Note that the macro compares the workbook with the macro (ThisWorkbook) and a second open workbook. Optionally, a dialog to open the second file, or even the two workbooks could be implemented at the start if need be.

Combine All Open Workbooks Excel VBA Macro

We often need to gather data spread across several Excel workbooks. The following VBA macro combines all open workbooks into just one. The final number of worksheets will be the sum of sheets across all open workbooks. We may want to add code to close the workbooks when done; we can do that simply adding wb.Close within the IF statement.

Separate Sheets Into Workbooks Excel VBA Macro

This macro example separates sheets into new workbooks, keeping the original workbook. Each new workbook takes the name of the sheet and contains one single sheet with its original contents.

Copy Selection To Other Workbook Excel VBA Macro

This is a simple example to copy the selected range into a newly created workbook. We use object variables to easily manage both workbooks. Then we save the new workbook in the same location and close it.

Open Workbook Safely Excel VBA Macro

Excel VBA generates and error if the file we try to open does not exist. It also display an alert and breaks the code if the file is already open. This macro checks for those two conditions before actually opening the file.

List Defined Names Excel VBA Macro

Excel allows to define names that can be used in formulas instead of cell references. This macro example lists all defined names and formulas in a workbook.