Auto creation of sheets based on Data sheet with a sample output shown next to it

**paradise2sr** · 11-26-2013, 01:07 PM

Hi,
Let me explain my problem.As stated in my below link,I have 2 sheets.They are as follows :

-' Data ' Sheet
- Sample output sheet based on 'Data' Sheet

Here in 'Data' sheet I have 87 parties in column B, Address in column C,PAN in column D,sales value in column E,Debit in column in F and Credit in column G.Based on data sheet,I want to create automatically all the 87 sheets linked with column B,C,D,E,F,G,data.A sample of one party is shown after "Data" sheet.In that sample output of one party worksheet,following links exist that is connected with 'Data' sheet which are -

G3,A7,A8,A9,A13,A14,A15 .These are variable whereas all other matters are constant.

If you require further info,do let me know.
crosspost:
http://www.vbaexpress.com/forum/show...857#post300857

With Best Rgds,
suresh

**paradise2sr** · 11-26-2013, 01:19 PM

Below is the attached workbook.

**JOHN H. DAVIS** · 11-26-2013, 05:01 PM

Maybe:

Please Login or Register  to view this content.

**paradise2sr** · 11-26-2013, 11:17 PM

Dear Sir,

Thanks for the reply.It did not do few things from your given code-

1.It did not change / refreshes the sheets when anything I changes in the main 'Data' sheet
2.It did not displayed column G data as 'Credit Closing Balance' in the generated sheet when Column F data 'Debit Closing Balance' is Nil or has no value
3.Is it possible to use check Box control from A5 onwards in 'Data' sheet and filter option A4,so that when I check on A4,it automatically get checked all the data for automatically generating sheets and also when any party sheet (column B of 'Data' sheet) I did not like to generate I can manually uncheck all those check box which is below A4 i.e A5 onwards.
4.Where there is no data,instead of displaying '0' should be display '-'

Hope this might get solved too.

With Best Rgds,
suresh

**jindon** · 11-27-2013, 12:13 AM

This should do it.

**paradise2sr** · 11-27-2013, 03:36 AM

Dear Sir,

Pls refer to your enclosed worksheet,you have changed the data of column G5 & G6 of 'Data' sheet.When there is no data in column F, then there should be data in column G which represents Credit Closing Balance.This things you have not taken into consideration while generating the various sheets.You have left blank stating as- 'Debit Closing Balance: Rs. /- '.Here, now should be 'Credit Closing Balance:Rs. ' with the amount in column G becoz either F4 column is blank or G4 column is blank and accordingly Debit or Credit Closing Balance would be displayed in generated sheet.

Also you have not stated about check box.

I think now you have understood.

With Best Rgds,
Suresh

**jindon** · 11-27-2013, 03:47 AM

Are you talking to me?

Originally Posted by paradise2sr

you have changed the data of column G5 & G6 of 'Data' sheet

Then change back to the original.

**paradise2sr** · 11-27-2013, 04:04 AM

Dear Sir,

Besides that,kindly take into consideration remaining factor that I have explained in the #6.

With Best Rgds,
Suresh

**jindon** · 11-27-2013, 04:51 AM

Then you should change the formula in the "Template" sheet.

1) Go to VBE and select Sheet2(Template) from Project Explorer
2) change the Visible status from VerryHidden to Visible

So that you can see the Template Sheet in Excel.

The code is only changing Cell reference within the formula when new sheet is added.

e.g
=Data!F4&" Closing Balance: "&"Rs. "&Data!F5&"/-"
will be change to
=Data!F4&" Closing Balance: "&"Rs. "&Data!F7&"/-"

So, if the formula is capable to handle what you said, all will be fine.

**paradise2sr** · 11-27-2013, 06:51 AM

Dear Sir,

Thanks for the reply.Here Data!F4 & Data!F5 should hold the condition when F5 has value.But when F5 has not value then it should Data!G4 & Data!G5 should hold the condition.This how would you incorporate in the formula that you have suggested.As this part is lagging.

With Best Rgds,
Suresh

**jindon** · 11-27-2013, 07:18 AM

As I said already, it is a problem of your formula.

As I really don't know how you want the conditions/result in that cell, it will be your job.

**paradise2sr** · 11-27-2013, 07:56 AM

Originally Posted by jindon

As I said already, it is a problem of your formula.

As I really don't know how you want the conditions/result in that cell, it will be your job.

Ok,thanks for your reply.I will be eagerly waiting that someone might solve this.

With Best Rgds,
suresh

Auto creation of sheets based on Data sheet with a sample output shown next to it

LinkBack

Thread Tools

Rate This Thread

Display

Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Re: Auto creation of sheets based on Data sheet with a sample output shown next to it

Thread Information

Users Browsing this Thread

Similar Threads

Populate Output sheet based on input data and lookup condition

Compare two sheets of Data and output Variance on New Sheet

Code to copy data from multiple sheets into one final output sheet.

Importing several Output sheets into one consolidated Output Sheet

Compare data with in a couple of sheets then output to a blank sheet

Bookmarks

Bookmarks

Posting Permissions