VBA Code For Automatically Creating New Worksheet

[central_boy_wonder]

Hi there,

Having a bit of trouble with VBA code for creating a new worksheet. Here's what I'm looking to do:-

- I have a Master worksheet and want to automatically create a new worksheet every time I input a value into each cell in the range A7:A22.
- The name of the new worksheet would be the same as the value inputted.
- The worksheet being created would be a copy of a worksheet called "Template".
- I've had trouble with the Template worksheet becoming hidden after a new worksheet is created but I want it to remain visible.

I'm not sure f this needs to be worked into the same VBA code, but I also want the cells in range A7:A22 automatically hyper-linked to the matching worksheet.

Any help would be greatly appreciated!

[BadlySpelledBuoy]

Try this in the module for the "Master" sheet.

Please Login or Register  to view this content.

BSB

[Joske920]

Hi,

Give this a try

put the following code in the sheet with the A7:A22 range, code area

Please Login or Register  to view this content.

[central_boy_wonder]

Thanks, that's fantastic!

One issue is that if I delete multiple cells at once, from the range on the master sheet, the attached error message comes up.

Attachment 723581

Also, the order of the new sheets created is in reverse i.e. cells A7, A8, A9 are shown in order of A9, A8, A7. Is they was to have them in order created?

Many thanks

[central_boy_wonder]

Try this in the module for the "Master" sheet.

Please Login or Register  to view this content.

BSB

Thanks BSB,

Had a couple of errors come up when running this.

This with the hyperlink.

Attachment 723585

And this when deleting cells from the range

Attachment 723586

Many thanks

[central_boy_wonder]

Hi,

Give this a try

put the following code in the sheet with the A7:A22 range, code area

Please Login or Register  to view this content.

Thanks, that's fantastic!

One issue is that if I delete multiple cells at once, from the range on the master sheet, the attached error message comes up.

Attachment 723587

Also, the order of the new sheets created is in reverse i.e. cells A7, A8, A9 are shown in order of A9, A8, A7. Is they was to have them in order created?

Many thanks

[Joske920]

Hi,

I cant see the attachment 723587

How did you delete the cells because i have deleted them in diferent ways and dont get a error

[central_boy_wonder]

Hi,

I've tried to attach it again.

Attachment 723598

I shift select a few cells on the master sheet (say A7:A9) and then press delete. It comes up " Run-time error '13': Type mismatch " then in the VBA " If Target.Text = vbNullString Then " is highlighted yellow.


I'm also still struggling to find a way to sort the order of the new sheets.

Thanks

[central_boy_wonder]

Also, if I insert a column within the master worksheet, the same run-time error and highlighted VBA comes up

Thanks

[central_boy_wonder]

Hi all,

It's now all working!

I added " If Target.Cells.Count > 1 Then Exit Sub " as the first line and it's resolved all the error issues.

The worksheet order is also resolved.

Thank you both for your help!