Sat 23 Sep 2017

VBA - Macro to Combine (Append) Sheets

By |Saturday, September 23rd, 2017|Categories: VBA|Tags: , , , , , , |0 Comments

Many times, we need to combine worksheets together. Below is a Macro to do this.

You just need to change the parameter in "Change Parameters in this Section".

  1. In case of many sheets, it will merge all sheets.
  2. If you don't want to merge all sheets but few sheets, just create two sheets named "Start" and "Finish" and move all sheets between these Start and Finish. The macro will merge all sheets between Start and Finish.

(more…)

Sat 26 Nov 2016

Tips & Tricks 150 - Save Each Worksheet as Different Workbooks

By |Saturday, November 26th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

You have got a workbook and you want to save each worksheet as different workbook - You can adopt following method to do so if worksheets are not many in number.

1. Right click on a worksheet tab
2. Move or Copy
3. Select new book
4. Save this new workbook
5. Do it for all 20 worksheets.

(more…)

Sat 12 Dec 2015

Tips & Tricks 122 - Always Open a Specific Worksheet when Workbook is Opened

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

This can only be accomplished through VBA.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Put following code in ThisWorkbook (Replace Sheet Name appropriately)

 

Sat 12 Dec 2015

Tips & Tricks 121 - Macro to Protect / Un-protect All or Selective Sheets

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the below code in this
6. ALT+F8 to display Macro Window
7. Run your Macro from here

'******* Protect / Un-protect Sheets *******

 

Sun 25 Oct 2015

Article 28 - How to Unhide all Tabs (Worksheets)

By |Sunday, October 25th, 2015|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same.

Option 1 - Use Custom Views

1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the same also)
2. Views tab > Custom Views > Add and give this view a name (for example "ShowAll")

(more…)

Sat 08 Aug 2015

Tips & Tricks 104 - Hide a Sheet Securely

By |Saturday, August 08th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

You want to hide a sheet so that nobody else can open that. This may arise in some situations like you have a dump of employee data which has sensitive information like salary, last rating, age etc. You want to keep a copy of this in your workbook but you want to refer to only few fields which are non-sensitive in nature. Then, you can resort to below trick -

1. Save your sheet as macro enabled i.e. with .xlsm extension.
2. ALT+F11 to open VBA window (This can also be opened by taking right click in the tab and choosing View Code)
3. Locate your workbook name in Project Explore window which is in top left corner. If this is not available, you can either do CTRL+R or View > Project Explorer
4. Click your sheet.
5. You have Sheet Properties window below Project Explorer. If not available, do F4 or View > Properties Window
6. Last option in Properties Window is Visible, which you should set to 2- xlSheetVeryHidden

1

7. Go back to your workbook / sheet in Project Explorer. Right Click and Insert Module. - This is an important step and many people generally forget about this step.
8. Tools > VBA Project Properties > Protection tab - Check Lock Project for Viewing and put passwords.

1

Now, your sheet can be unhidden only by those persons who know the password for your VBA project.

Sat 11 Oct 2014

Tips & Tricks 59 - Remove Comments from All Sheets in a Workbook

By |Saturday, October 11th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

In an earlier tip, I talked about how to remove all comments from a sheet. But that works on a sheet. If you want to remove from all sheets in one go rather than doing for every sheet separately, use following trick -

1. Office Button > Prepare > Inspect Document

1

2. A warning message will come, press Yes.1

3. In the next box which comes, DESELECT all box except Comments and Annotations. Press Inspect.1

4. If comments are found, it will say so. Press Remove All. This will remove All comments from the Workbook.1