Creating worksheets from a database

[EXCELlant]

I have a contract in an excel worksheet and a list of 50 names in a database (in Column A).

is there a way to duplicate the contract to make 50 tabs with each contract having the persons name from the database?
e.g Contract 1; Mr Smith
Contract 2; Mr Brown

the name need to populate in cell D4 of the contract.

Any help or suggestions would be great.

Thanks!!!

 

[ScottR]

What's the sheet name of your database sheet and is the contract another sheet? If so what's its name?

 

[EXCELlant]

Hi Scott,

There are two seperate sheets 'Database' with the list of 50 names and currently one template of the contract labelled 'Master'.

i was thinking of the VBA creating duplicates of the 'Master' each with it own unique name from the 'Database'.

What do you think?

Thanks for your help

 

[ScottR]

I've assumed your list of names starts in Row 2 on the database page (if not adjust the value of 'x') and have left it dynamic in case the list size changes from 50.

Sub CreateContracts()

Dim dbSh As Worksheet, shCnt As Integer
Dim tSh As Worksheet
Dim cSh As Worksheet

Set dbSh = ThisWorkbook.Sheets("Database")
Set tSh = ThisWorkbook.Sheets("Master")

x = 2
Do
    If dbSh.Cells(x, 1) = "" Then Exit Do
    shCnt = ThisWorkbook.Sheets.Count
    tSh.Copy After:=Sheets(shCnt)
    Set cSh = ThisWorkbook.Sheets(shCnt + 1)
    cSh.Name = "Contract; " & dbSh.Cells(x, 1)
    cSh.Cells(4, 4) = dbSh.Cells(x, 1)
    x = x + 1
Loop

End Sub

 

[EXCELlant]

SUPERB! thats exactly what i needed!

im trying to get more familiar with vba but unfortunatly dont understand this coding.

thanks for all of your help though Scott.

 

[ScottR]

You're welcome.

Depending on your current level of knowledge playing around with the macro recorder is a good place to start. Just keep in mind it is a deeply flawed tool that produces inefficient code and won't teach you anything about looping but it will give you insight in the Excel Object Model and its properties, events etc.