Copied numbered Sheet based upon cell value

[DeanGilgrist]

Hi folks,
I can usually find the information on these forums that I'm after, but this time I have come up dry..

I'm looking for a VB formula that will duplicate a sheet based on a cell value. The cell value will be a number (1-12) and the sheets have all been renamed to numbers (1-12).

I'd like the formula to; IF G1 (from a sheet named 'Fighter Data') = 2 THEN DUPLICATE SHEETNAME "2".

Thanks in advance lovely people.

Dean

[Kaper]

try:

Please Login or Register  to view this content.

[DeanGilgrist]

Hi Kaper,
Thanks for the fast response. I am getting 'subscript out of range'. Please see attached.

Regards

Dean

[Kaper]

There was an error in address (G2 not G1 !) (it was in my original post, which I edited probably 30 seconds after posting :-) )
By the way there are 2 other minor items
- as you use SUBTOTAL in G1 (which returns number not text, it will be wiser to use value from that cell converted to text.
- I see no point to insert this macro in ThisWorkbook module, not in standard module.

Best,

[DeanGilgrist]

Hi Kaper,
Thanks for the help. I think I need to teach myself VBA as I'm rubbish at that/ Why would you say put the code in the sheet rather than the module? What detriment would it have? And since we are referencing 'G1' would that mpt already be referencing that cell? How would making it text be any better?

Thanks in advance

Dean

[Kaper]

Hi number of questions, only few easy answers:
1) I've not said to put code in the sheet. Just opposite, I suggested to put it in standard module (Module1) - It was originally in workbook code (which in this case made no harm, but is not a good practice). In the Workbook code is a standard place to keep event handlers for workbook events (happening on open, beforeclose, beforeprint, activate etc.). All standard code would fit fine in standard code module.
2) converting to text. Name of the sheet is text. But Sheets(variable) accepts both text with sheetname and just number as an index. so if your workbook have 3 sheets named" 3, SheetX and Summary
Sheets(3).activate will activate third sheet named Summary, while Sheets("3").activate will activate first sheet named 3.