I think this may do what you want:-

Sub CopySheets()
Dim X As Integer
Dim Y As Integer
If Sheets("Lead").Range("G1").Value = "Master" Then
For X = 1 To Sheets("Lead").Range("B34").Value
For Y = 2 To 5
Sheets(Y).Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
Sheets(ActiveWorkbook.Sheets.Count).Name =
Sheets("Lead").Range _("F" & 36 + X).Value & " - " & Sheets(Y).Name
Next Y
Next X
End If
End Sub

I have assumed that you only have 5 sheets that are labelled "Lead",
"Header", "Quality", "Detail", "Variance" and that the "Lead" sheet is always
the first sheet.

I was unsure what you meant by "For the first new Header sheet copy value in
cell F37". I have assumed that you meant that you wanted the sheet to be
named after the value in F37 so I have named the copied sheets as a hybrid of
the Orignal Sheet name (e.g. Header, Quality etc) plus the value in F37, F38
etc so if F37 = "Bob" and F38 = "Fred" you will get new sheets named "Bob -
Header", "Bob - Quality" etc......"Fred - Header", "Fred - Quality" etc.......

If it isn't exactly what you require it should, hopefully, point you in the
right direction

HTH

Regards

Andy W

"thesaxonuk" wrote:

From previous work I have established certain facts relating to my data. I
now would like to copy a number of spreadsheets based on these facts and auto
populate data from the lead spreadsheet.

e.g.
Lead Spreadsheet Has field that is "Master" or "False" in G1
And Number of Addtional Records in field B34 Variable number from 1 to 25

Task
If G1 = "Master" then copy a group of sheets (Header, Quality, Detail,
Variance) the number of times stated in B34
For the first new Header sheet copy value in cell F37
For the next Header sheet copy value in cell F38
and so on until Number in B34 has been reached.