automatic sheet generation

[singoi]

Hi Friends,

i hope i get the required help from this forum. here is my problem
actually i have only two sheets..first sheet is the user sheet where the user gives the data...second is the sheet where in we get the result according to user given data..

there is a cell in User sheet where User gives a Number. for example " 8 "

so sheet2 : here we have only 9 rows. as user gave "8" the fisrt 8 cells in the first column should see like this

B 01/08 , B02/08 , B03/08 as so on till B08/08

when user gives 9 in user sheet

then the first sheet is finished with all the 9 rows ending with B09/08

when user gives 10...then sheet 3 should automatically appear with the first row and first colum saying B10/08.

when user gives 20 in user sheet...then sheet4 should appear with last number as B20/08

so each sheet is having only 9 rows..

first sheet ends with B09/08
second sheet ends with B18/08
third sheet ends with B27/08

how can i write the code and where should i write the code..can anyone help me pleaseeeeeeee

[royUK]

I don't think you will do this with a Function. Maybe you should attach an example workbook.

[singoi]

attaching a jpeg file.

please i dont think this should be complicate question..

need help my friends...

[rwgrietveld]

I've used this VBA code

Please Login or Register  to view this content.

It was made an Automatic function by implementing

Please Login or Register  to view this content.

in Worksheet1. Look at the code in the attachement. I did not include the check is the Worksheet exist (If not add worksheet ...)

[singoi]

Hi Friend,

i am attaching my project here..maybe u can see here..

there u can see " Number of Welds" this should be given by user..maybe 50...
according to this number the number of sheets should be added..and each sheet will be consisting only 9 Keys..u can find them in sheet1..there are they Keys as B01/08 , B02/08 and so on..if USER gives 13 in USER Defined...then we have 4 sheets...

Sheet1 till B09/08 and Sheet3 till B13/08

sheet3 and sheet4 are added later accordingly ....

Please i think u can help me...

[Cheeky Charlie]

So you want a (macro) which creates (number/9) worksheets (according to a template, I assume) and populates them with:
1-9, 10-18, 19-27....(number-8)-number

A macro which creates these sheets could be very destructive if -
a) someone puts 900 in the relevant box
b) it creates sheets every time a value is entered

What do you think about these dangers?

[singoi]

Thank you my friend...

users can use the max number od 60..that means 7 sheets...

not more than 60 will be entered...
as soon as the user enters this numer 1 till 60 .. sheets should be created accordingly using 9 per sheet...

and the first row should be filled accordingly
B01/08
B02/08
........and so on B45/08.......B60/08

the first page will be filled by user and the rest should form automatically...

thanks...

[rwgrietveld]

Seeing this I would like to propose the following.

1) make 60/9 = 7 sheets
2) Use Worksheets function in the cells H3, K3, ...
3) Hide Those sheets that are not used

The will result in a very simple macro and sheets that are maintainable.

This way you would overcome the dangers defined by Cheeky Charlie
Let us know.

Could you provide for an "Empty" template (Sheet1, Sheet2 ...)

[singoi]

Hi friend,

atleaset how can i change these numbers in this type..only the starts should get increased from 01 till the number given in USER Sheet and also in row format...

B**/08

Thanks...

[DonkeyOte]

new x-thread for reference:

http://www.mrexcel.com/forum/showthr...=1#post1734607

[singoi]

Sorry friends and Moderator...

i have posted the same question somewhere else in need of answer..sorry..

friends..i wanna make my problem little bit easier and uncomplicated..

please need help from anyone..

i have a command button on main page..." Continue "

and a box where User enters a value in Welds and pages...for this example will be pages till 6 and Welds till 27

now as the user gives the value between 1 and 9 and presses command box "Continue " sheets weld2 and weld3 should get hidden and only weld1 should be visible with some values updated.

in the sheet Weld1 on top row...there are some values given like
B**/08 ... now in Weld1 there will be only till B09/08
so these should get automatically updated with corresponding number. when User gives only 4 in first page welds...the values in Weld1 should be only untill B04/08

so when User gives the value of welds in first page between 10 and 18 weld1 and weld2 should be visible with values updated and sheet weld3 should be hidden..

please friends..i am running out of my time..please can anyone help me in giving me the code for thsi command button....

Thanks in advance - singoi

[rwgrietveld]

See how you like this.

To build B**/08

Please Login or Register  to view this content.

takes the sheetnumber weld1 ..weld7 will be 1 .. 7. As you have merged cell I've used the following formula series y = 1/3 * Column() - 5/3
Column H (8) will be 1
Column K (11) will be 2
Column N (14) will be 3
It will only be displayed if the number < Welds (defined name) otherwise ""

The code will just show/hide those sheets that are required.

Please Login or Register  to view this content.

and this function is called when J25 is changed

Please Login or Register  to view this content.

Please check your 'circular references"

[singoi]

danke....es hatt geklappt...vielen dank...

[rwgrietveld]

My german is a bit rusty, but I hope you say thanks for the effort. Please set this thread as SOLVED (edit your first post)