Excel 2010 Macro to copy worksheet, update formula and rename worksheet

For some reason I am having a really hard time getting what I believe to be a very simple macro to run.

There is one workbook with two worksheets. First worksheet is for data entry and called “Input Data Here”. The second worksheet is a form – it takes all the data from the input page and makes it look pretty. That worksheet is called “PAR”.

The macro should do 3 things

1. Duplicate “PAR” until the last empty row on the “Input Data Here” tab. I thought I had this working with the below code, but now I'm getting a 424 error - Object Required

Please Login or Register  to view this content.

2. Change a formula in cell X8 on “PAR” to advance to the next row on “INPUT DATA HERE”. The formula in the first worksheet is ='INPUT DATA HERE'!A2 – with each new copied worksheet I want it to advance one row.

3. Rename the worksheet to a cell reference. I think I have that working with this code, but don’t know for sure because I can’t get steps one and two to work correctly so I keep getting the error that says I can’t have duplicate sheet names.

Please Login or Register  to view this content.

Any help is much appreciated.
Suzi