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
Shaolin1976
|