Adding and renaming worksheets using VBA

[hvisa]

I have 2 message box in which the start year and the number of years should be entered by the user . Depending on the number entered (number of years) new worksheets should be created ... Let us say number of years=5, then 5 new worksheets which replicates the existing worksheet should be created.
Depending on the start year entered, this worksheets needs to be renamed as Year 2007-year 2011(if n=5).. How do I do this ...

I can't use

Worksheets("Year 2007").Copy after:=Worksheets(.Count)
It gives me an error- Subscript out of range...

I do not know how to link the start year, number of years with add sheet and rename sheet... Any help will be appreciated..

[MSP77079]

Not clear which worksheet you want to copy. Here is how you could add a worksheet and name it.

Please Login or Register  to view this content.

Copy should work as well. But, you have to copy a sheet that already exists.

Please Login or Register  to view this content.

Hope this helps. Let me know if I was missing something.

[Leith Ross]

Hello Hvisa,

This macro will ask for the starting year and number years. Both are checked to determine they are numbers. If not a message is displayed and the user is prompted again to enter the data. The new worksheets are added after the last worksheet and named "Year " and the year. Add a Standard VBA Module to your project then copy and paste the code into it.

Please Login or Register  to view this content.

sincerely,
Leith Ross

[hvisa]

Thanks for your response. Leith, your answer was the one I was looking for.
Thanks
hvisa

[hvisa]

Can you help me with second part of the problem too!
Now we have created the spreadsheets, let us say these worksheets are in 'years'excel workbook.
In the attached 'data' worksheet , i need to copy values from column E-H for each year and paste it in another excel spreadsheet- Years according to the name of the year.
For example, in the 'years' spreadsheet, i have sheets like Year1(2007), year2(2007),year1(2008) and goes on ...
Now in this 'data' sheet, if the Year is 2007 and col_ID=1, then i need to copy values from E-H till mnth_ID=12 and paste the values(transposing it)in the Year1(2007) sheet in the 'years' spreadsheet.
Similiarly,if the col_Id=2 and year=2007, then I need to paste it again in Year2(2007) spreadsheet(transposing the values).

Please help!
See attached!