Macro for Creating WorkSheets

[vinayguj]

Hi All,

I need help for the below activity. I am not well versed in Macros. Below is the scenario.

1. I have one Workbook, with two Worksheets named as; "People" & "Masterfile"

In People Worksheet the Data is as below:

	Column A	Column B	Column C	Column D	Column E	Column F
Row 1	Reg No.	Name	Credit	Debit	Reason	Week Nos.
Row 2	1	Ajay	10		To buy stationary	5
Row 3	2	Vijay	30		To buy stationary	5
Row 4	3	Sujay		50	Paid back pending	5
Row 5	4	Ajay	40		Paid for transport	6

<tbody>
</tbody>

Masterfile worksheet has following data:

	Column A	Column B	Column C	Column D
Row 1	Name
Row 2
Row 3	Transactions Reason	Week Nos.	Debit	Creadit
Row 4
Row 5

<tbody>
</tbody>

The Macro should
1. Create number of "Masterfile" worksheets based on the number of Names in the "People" worksheet, and rename the worksheet with the name, but if already created, then do not create again.
For Example: Worksheets by Name: "Ajay", "Vijay", "Sujay"

2. In the Worksheet "Ajay", the details of Name, Transaction Reason, Week Nos., Debit, Credit, should be filled corresponding to the Data in the "People" Worksheet. For Example:

	Column A	Column B	Column C	Column D
Row 1	Name	Ajay
Row 2
Row 3	Transactions Reason	Week Nos.	Debit	Creadit
Row 4	To buy stationary	5		10
Row 5	Paid for transport	6		40

<tbody>
</tbody>

3. Similarly in other worksheets of Vijay and Sujay.

I hope somebody helps at the earliest.

Thanks in Advance.

 

[vinayguj]

Yes Dave, there is other data as well in the "Masterfile" worksheet.

Precisely, the output in of the worksheets would be looking someting like the below.

Row 1	Column A	Column B	Column C	Column D	Column E
Row 2
Row 3	Name	Ajay
Row 4
Row 5
Row 6
Row 7
Row 8		Week No.	Week No.	Credits	Debits
Row 9
Row 10
Row 11
Row 12
Row 13
Row 14
Row 15
Row 16
Row 17
Row 18	Transaction Reasons
Row 19		1	1
Row 20		2	2
Row 21		3	3
Row 22		4	4
Row 23	To buy stationery	5	5	10.00
Row 24	Paid for Transport	6	6	40.00
Row 25		7	7
Row 26		8	8
Row 27		9	9
Row 28		10	10
Row 29

<tbody>
</tbody>

All the Blank Cells inbetween has some other data which needs to be carried onto all the worksheets.
All the words in bold would already be in the "Masterfile", just as a Template.

The Name, Transaction Reason, Credits, Debits is what the Macro should copy and paste from the People WorkSheet but corresponding to that person.

Above is an example I have shown for Ajay.

I know, I am making it very complex now. I am sorry, I should have given these details earlier.
Sorry for the trouble.
Thanks a million for your efforts and help!

 

[vinayguj]

Thanks you very much for your help Bertie.

 

[dmt32]

Yes Dave, there is other data as well in the "Masterfile" worksheet.

Precisely, the output in of the worksheets would be looking someting like the below.

Row 1	Column A	Column B	Column C	Column D	Column E
Row 2
Row 3	Name	Ajay
Row 4
Row 5
Row 6
Row 7
Row 8		Week No.	Week No.	Credits	Debits
Row 9
Row 10
Row 11
Row 12
Row 13
Row 14
Row 15
Row 16
Row 17
Row 18	Transaction Reasons
Row 19		1	1
Row 20		2	2
Row 21		3	3
Row 22		4	4
Row 23	To buy stationery	5	5	10.00
Row 24	Paid for Transport	6	6	40.00
Row 25		7	7
Row 26		8	8
Row 27		9	9
Row 28		10	10
Row 29

<TBODY>
</TBODY>

All the Blank Cells inbetween has some other data which needs to be carried onto all the worksheets.
All the words in bold would already be in the "Masterfile", just as a Template.

The Name, Transaction Reason, Credits, Debits is what the Macro should copy and paste from the People WorkSheet but corresponding to that person.

Above is an example I have shown for Ajay.

I know, I am making it very complex now. I am sorry, I should have given these details earlier.
Sorry for the trouble.
Thanks a million for your efforts and help!

In this case it may be better to adapt code bertie kindly posted.

Dave

 

[Faustin]

Hi To All!
i have created a user form using VBA in excel for budget preparation, but i have challenge to add VBA codes than can link data in the form into excel report,
can someone help!

Thanks.
Faustim

 

[bertie]

Hi To All!
i have created a user form using VBA in excel for budget preparation, but i have challenge to add VBA codes than can link data in the form into excel report,
can someone help!

Thanks.
Faustim

Hi Faustim, and welcome to the forum.

Can I please ask you to start a new thread for your problem. Feel free to drop me a pm to with a link to the thread.

You will find you will get a faster response if you provide more detail.
Is the form on a spreadsheet or is it a UserForm?
How do the form cells/controls map to the report? i.e.,

cell/control on form => column A on report,
cell/control on form => column B on report, etc.

If it is a UserForm please provide the names of your controls.
[Edit:] The name(s) of your spreadsheets would also be helpful.

Thanks,
Bertie

 

[bertie]

Hi Vinayguj,

I have amended the output of my code (see the "Process Name" section) based on the layout of the masterfile sheet in post#11.

try this:

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]


[COLOR=darkblue]Sub[/COLOR] CreateNamedWorksheets()
   [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet     [COLOR=green]'new worksheet[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range           'People sheet loop range
   [COLOR=darkblue]Dim[/COLOR] SheetName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]    [COLOR=green]'new worksheet name[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]             [COLOR=green]'next available row[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] weekNum [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   
   [COLOR=green]'sort the data[/COLOR]
   SortColumnB
   
   [COLOR=green]'loop through the data until column B(name) has no value[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rng = Sheets("People").Range("B2")
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      
      [COLOR=green]'check the sheet exists[/COLOR]
      SheetName = rng.Value
      [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] WorksheetExists(SheetName) [COLOR=darkblue]Then[/COLOR]
         Sheets("Masterfile").Copy After:=Worksheets(Worksheets.Count)
         ActiveSheet.Name = SheetName
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(SheetName)
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(SheetName)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]


      [COLOR=green]'process name[/COLOR]
      wsNew.Range("B3") = SheetName
      
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] rng.Value = SheetName
         [COLOR=darkblue]With[/COLOR] wsNew
            rw = 18 + rng.Offset(, 4).Value
            .Range("A" & rw).Value = rng.Offset(, 3).Value  [COLOR=green]'transaction[/COLOR]
            .Range("D" & rw).Value = rng.Offset(, 1).Value  'credits
            .Range("E" & rw).Value = rng.Offset(, 2).Value  [COLOR=green]'Debits[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
         
         [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
      [COLOR=darkblue]Loop[/COLOR]
      
   [COLOR=darkblue]Loop[/COLOR]
   
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] SortColumnB()
   Worksheets("People").Range("B1").Sort _
        Key1:=Worksheets("People").Columns("B"), _
        Header:=[COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]






[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Function[/COLOR] WorksheetExists([COLOR=darkblue]ByVal[/COLOR] SheetName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
      WorksheetExists = Sheets(SheetName).Name = SheetName
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[Faustin]

Hi Faustim, and welcome to the forum.

Can I please ask you to start a new thread for your problem. Feel free to drop me a pm to with a link to the thread.

You will find you will get a faster response if you provide more detail.
Is the form on a spreadsheet or is it a UserForm?
How do the form cells/controls map to the report? i.e.,

cell/control on form => column A on report,
cell/control on form => column B on report, etc.

If it is a UserForm please provide the names of your controls.
[Edit:] The name(s) of your spreadsheets would also be helpful.

Thanks,
Bertie

Thanks Bertie,
would you please advise how i can start a new thread, so that i can explain in details my question.
Thanks.

 

[bertie]

Thanks Bertie,
would you please advise how i can start a new thread, so that i can explain in details my question.
Thanks.

Excel Forum
Top left
Click on Post New Thread.

Once you have posted the thread
Go to the Excel forum - find the thread - right click and select - copy link address
Click on my name - select Send Private message - paste the link.

See here for more extensive guidelines.
http://www.mrexcel.com/forum/showthread.php?127080-Guidelines-for-posting-(updated-22-Nov-2006)

 

[vinayguj]

Bertie, I am just awestruck! Its workinng just awesome.! thanks a ton! Awesome!