Maintain/create many sheets

[kevinboo]

Hi All,

Need a little help. I did, some time ago obtain the code below, this automatically created, and maintained/updated many sheets from a Master sheet, but I now need a tweek.

When I origionally used this code the list of items on the Master were static, in that once the individual sheet had been created, only certain fields for the item on the master changed, but no additional lines were added to or taken away from the master.

Now I need to add lines to the master possibly on a daily basis, and have this change reflected on the individual sheets. The individual sheets (so the names in B2:B in the macro) are place names, so what I want is;

• If a sheet for say 'France' already exists, and I then add another line for France on the master, for the individual 'France' sheet to reflect this change.
• But if I add a line for say 'Britain' and no data for 'Britian' exists on the master, then I want it to create an individual sheet for 'Britian' and reflect the detail on the Master, anybody???

Origional code below;

This will <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>

• Create a new sheet<o></o>
• Pull all the info from the specific ‘Name”<o></o>
• Will update on opening<o></o>

<o> </o>
All the info from a ‘Master’ Worksheet<o></o>
<o> </o>
A couple of things<o></o>
<o> </o>

• The B2:B below points to the column that the ‘Names’ for the worksheets to be created are in .<o></o>
• Paste both macros into the workbook specific<o></o>

<o> </o>
Sub xxx()
Dim iCol As Integer, iColEnd As Integer
Dim lRow As Long
Dim rMaster As Range, R As Range
Dim sCur As String, sMess As String
Dim wsMaster As Worksheet
Dim wsTarget As Worksheet
<o> </o>
Set wsMaster = Sheets("Master")
Set rMaster = wsMaster.Range("B2:B" & wsMaster.UsedRange.Rows.Count)
iColEnd = wsMaster.UsedRange.Columns.Count
<o> </o>
'***************************************
'** first ensure that all sheets exist **
'***************************************
For Each R In rMaster
sCur = R.Text
On Error Resume Next
Set wsTarget = Sheets(sCur)
If Err.Number > 0 Then
Sheets.Add after:=Sheets(Sheets.Count)
Err.Number = 0
Sheets(Sheets.Count).Name = sCur
If Err.Number > 0 Then
MsgBox "Row " & R.Row & " has an illegal character in the name." & vbCrLf & _
"Please correct."
Exit Sub
End If
End If
On Error GoTo 0
Next R
<o> </o>
'*******************************************
'** Loop thru all entries in Master sheet **
'*******************************************
For Each R In rMaster
sCur = R.Text
Set wsTarget = Sheets(sCur)
lRow = wsTarget.Range("A65536").End(xlUp).Row + 1
For iCol = 1 To iColEnd
wsTarget.Cells(lRow, iCol).FormulaR1C1 = "='" & wsMaster.Name & _
"'!R" & R.Row & "C" & iCol
Next iCol
Next R
End Sub
<o> </o>
This code make it update on opening<o></o>
<o> </o>
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "Master" Then Exit Sub
xxx
End Sub