Automatically Inserting values in new sheets that are created

[Chris1976]

Hallo Everyone

I was wondering if you could help me once more.

I have created a index sheet to my workbook that consist of about 30 sheets. The "Index" sheet has the following codes written to it.

Please Login or Register  to view this content.

The first part of the code is connected to a command button - each time when it is clicked it updates my index sheet in Range "A" with all the sheets that are in the workbook, it also picks up a name change of the sheets and is linked to hyperlinks

The second part of the code is linked to the hyperlinks and when all the sheets are hidden (EXCEPT THE INDEX SHEET) and I select a sheet in range "A" it will open that sheet, once i click on the index sheet again the opened sheet is hidden automatically again. This is all to present to the user only one sheet (INDEX - User platform if you want to call it that)

The problem I have is that each time I create a new sheet and refresh the command button it adds the new sheet at the next open row in range "A" (lets say the sheet is added in cell A 14), this is all 100% however, I then have to manually insert the following : (Cell C14 = Region) (Cell D14 = District) (Cell E14 = Regional Manager) (Cell F14 - Area Manager) - this is fine because it is the user input form (names ,region and so on have to change for each sheet)

What I want to include in the code is that once cell range C14:F14 (on the "Index" sheet) is completed by the user it automatically ads that to the sheet that is just created (I WILL PLACE A NUMBER 1, 2, 3, and so on to the new sheets to be created so the code recognize that only sheets that start with a number should apply to this code) in stead of me having to edit each cell in the new worksheet by saying =Index!C14, =Index!D14, =Index!E14 , =Index!F14

the location/s on the new sheet from which the range C14:F14 is to be copied is

C14 Copied to new sheet J5
D14 Copied to new sheet J6
E14 Copied to new sheet C12
F14 Copied to new sheet J12

I have included/attached the index sheet - bearing in mind its not allocated to a specific sheet, its to all new sheets created

[harrisonland]

Would this do it, or have I misunderstood you?

Please Login or Register  to view this content.

If that's not what you meant, you'll have to post a more complete copy of your workbooks so we can see what's going on.

HTH
Tim

[Chris1976]

Hi Harrisonland

do i just copy that below my code

[Chris1976]

And also what will trigger that, does it happen automatically when the sheet is created or copied from a existing sheet or is it triggered when command button is selected for the index sheet to update with the new or amended sheet

[Chris1976]

Hi Harrison

I tried the code , it does not work

[harrisonland]

The code in post #2 has to go in the INDEX worksheet. It doesn't create the new worksheet - I presumed you'd already done that elsewhere. It triggers when an entry is made in any of columns C:F and checks if all cells in the current row have an entry. If so, it then copies the values to the new worksheet named in column A.

Please can you post a version of your worksheet (with dummy data only) that shows what you've done with that code and that demonstrates what you mean by "it doesn't work"?

Tim

[harrisonland]

Just had another look... should actually read

Please Login or Register  to view this content.

Sorry, my bad!

Tim

[Chris1976]

Hi Harrisonland

this works perfectly, exactly what I was looking for

Thank you very much , much appreciated

[harrisonland]

You're quite welcome... You might want to think what should happen if a value in one of those columns is deleted.

Please consider leaving feedback by clicking the "*"to the left of any helpful posts.

Thanks
Tim