p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   Adding new row, hyperlink and sheet with a button (http://p2p.wrox.com/showthread.php?t=67975)

Shaolin1976 May 8th, 2008 09:01 AM

Adding new row, hyperlink and sheet with a button
Good afternoon,

I am playing about with VBA in an attempt to improve my excel proficiency but I have a problem that i have been tackling for some time due to its complexity:

I have a workbook with a master sheet containing an overview of data for the subsequent x number of worksheets, each individual worksheet details an in-depth look at the data for each entry on the master sheet. My problem is this:

I would like to use a button on the master sheet to automate a few tasks all in one click:

    * Insert a new row at the 3rd to last row on the master sheet; it can't be the last row as this contains SUM(xx:yy) formulas.
    * Populate this new row with data identical to the row above, complete with formatting too.
    * Have a user input box come up asking for the name of the new entry and overwrite that inputted name to cell Bx where x is the new row number.
    * Insert a new worksheet at the end of x number of sheets and name it. The sheets are named as follows: "Master", "1", "2", "3"....."x" so ideally I would like the new worksheet to be named "x+1".
    * Create a hyperlink at new cell Bx linked to the new worksheet "x+1"
    * Copy and paste the contents of sheet "x" to sheet "x+1"

I have had limited success in automating this process in a stepwise fashion but i would like to have the single button do all commands in a single click, whether it be in the order i have listed in the bullet points or not is inconsequential i think. Any help or advice in tackling this problem in part or in whole would be greatly appreciated.

Many thanks in advance


All times are GMT -4. The time now is 01:28 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.