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 21 May 2016

Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Saturday, May 21st, 2016|Categories: Challenges|Tags: , , , , , , , , , , |0 Comments

This time, challenge is going to be tougher. An user receives a daily sheet which has got project status and you need to prepare a consolidated worksheet for the week everyday.

1. Your sheets are named Day1, Day2.....Day7.

2. One sheet can have a maximum of 20 entries.

3. The entries may not be same everyday depending upon whether a new project has started or a project has finished.

4. You need to get all the consolidation in "Consolidated Weekly Sheet".

One typical day's sheet -

1

You need to write formulas (or VBA) to populate row 3 onwards

1

 A typical answer would look like -

1

The worksheet related to this challenge can be downloaded from Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

The answer to the above challenge will be published after a month i.e. on 21-Jun-16.

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 04 Jul 2015

Article 17 - 3D Formulas - Hidden Wonder of Excel

By |Saturday, July 04th, 2015|Categories: Articles|Tags: , , , , , |0 Comments

3D formulas are one of the hidden wonders of Excel and not many of us know about the secret of this. The purpose of this article is to unravel the mystery of 3D formulas in Excel.

Suppose you have 4 worksheets called Quarter1, Quarter2, Quarter3, Quarter4 as below. And you want to find the total of Quarter1 to Quarter4 revenue. This is quite simple a task, just put the formula =Quarter1!B6+Quarter!B6+Quarter3!B6+Quarter4!B6 and you are through. But what happens when number of sheets are large say 12, 50 or 100. This becomes quite cumbersome to enter. And that is where the magic of 3D formulas comes into picture.

(more…)