Sat 18 Feb 2017

Tips & Tricks 156 - Get Workbook's Directory from Formula

By |Saturday, February 18th, 2017|Categories: Tips and Tricks|Tags: , , , |0 Comments

If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory for this would be

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)

Note - For this formula to work, you workbook must be saved at least once.

 

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)

Private Sub Workbook_Open()
    Worksheets("Sheet Name").Activate
End Sub