View Single Post
Old May 8th, 2008, 09:01 AM
Shaolin1976 Shaolin1976 is offline
Registered User
Join Date: May 2008
Location: Liverpool, Merseyside, United Kingdom.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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