VBA code for 300 booking nos in 300 different sheets

[rush2rajen]

Hi Excel / VBA Gurus,

I am having a sheet wherein there are 300 different booking nos in one master sheet named "data" and other one is "output" where I need to fill all the data from master sheet.
I need to make 300 different sheets in one workbook.

Is there any way through VBA coding that I can do this easily.

Your help will be highly appreciated

Thanks
"RS"

 

[NARAYANK991]

Hi ,

I doubt that it will be worth anyone's while to invest time and effort to write code to do what you want.

The easiest way out would be to enter simple formulae in the output tab to display the static information of the 15 columns ( columns B through P ) in their respective places , and enter ABCD000 in the Booking No. slot.

Take 300 prints of this filled-in form , and then manually write the numbers 001 through 300 in the Booking No. slots.

Doing this may take the same time.

Narayan

 

[rush2rajen]

Hi Narayan,

Thanks a lot for the suggestion I tried do it by simple formulae but its not working. I only need to show in the excel sheet and not to take the print out.

I would appreciate if you can help with some excel formulae to get this thing done. I would rather manually insert 300 sheets and put a formula to ease my work.

"RS"

 

[Hui]

If you want to extract data according to a field into 300 sheets try:

Move Data to other Sheets
http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/

Advanced Filter Move Data to other Files
http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/

 

[rush2rajen]

Hi Hui & Narayan,

I don't want you to work for 300 sheets. I would request you to help with VBA for one single row from master sheet and the result will show in output for the rest i will be able to do so.

Thanks
"RS"

 

[NARAYANK991]

Hi ,

If you wish to create worksheets from the template , the easiest way is to do the following :

1. Enter formulae to put the information from the data tab into the output tab in the appropriate cells ; I can certainly do this and upload the resulting workbook.

2. Convert the formula results to values.

3. In Excel options , increase the number of sheets in a new workbook to its maximum limit ; this may be 255.

4. Create a new workbook ; this will now have 255 blank worksheets.

5. Open the workbook from step 1 , and copy the cells A1:G17.

6. Select the newly created workbook , and select all the worksheets from Sheet1 through Sheet255 ; Paste the copied cells.

Adjust the column widths as desired.

All 255 worksheets will now be populated with the data from the template , except for the 3 digit number , which you will have to manually type in ; probably for this , simple code can be written to do this.

See the uploaded files.

Narayan

 

[rush2rajen]

Hi Narayan

Thanks a lot


Thanks
"RS"

 

[rush2rajen]

Hi Narayan
I had tried changing the Booking no Shipment no and date
as well as veh type & size but its not working after I am running the code.

It is taking some other data

Thanks
"RS"

 

[NARAYANK991]

Hi ,

Can you upload a few workbooks , which can clearly show what you want to do ?

I am not able to understand what you want to change , where , and how this should reflect in the individual worksheets.

Narayan

 

[rush2rajen]

Hi

Please find the attached file
I had changed the figure in tb1 and the result is not coming
Thanks in advance

 

[NARAYANK991]

Hi ,

This is not what you had posted initially !

In your initially uploaded file , only the booking number was changing with each row ; all other information remained the same.

In this upload , 6 columns of information are changing with each row.

My earlier posts had all assumed that only the booking number will change ; if more than one piece of information is going to change , then you will need VBA code to do what you want.

Probably you can use Hui's suggestions.

Narayan