Loop Macro a variable number of times
 

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.

Loop Macro a variable number of times
 

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.

Loop Macro a variable number of times
 

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

Worksheet 1 tab name "Header" Info in Cell G1 = "Master" Cell C27 has Count
of sub refs, Cell F37 has first sub ref
Worksheet 2 tab name Summary
Worksheet 3 tab name Quality
Worksheet 4 tab name Detail 1
Worksheet 5 tab name Detail 2
Worksheet 6 tab name Variance

All need to be copied when macro runs to create

Worksheet 1 to 6 as a new set of sheets auto tab name "Header 2" etc is okay
copy from Header F37 into Header 2 C1 then for the next group of sheets i.e.
Header3 copy Header F38 into Header 3 C1 and so on until finished.

"Andy Williams" wrote:

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

Ok, now I understand.

Try this (I've added comments to try to explain what is going on):-

Sub CopySheets()
Dim X As Integer
Dim Y As Integer
'Test to see if Header sheet cell G1 = Master
If Sheets(1).Range("G1").Value = "Master" Then
'Set the number of Repeats by getting the value of Header Sheet Cell
B34
For X = 1 To Sheets(1).Range("B34").Value
'Set a repeat for each of the 6 original sheets
For Y = 1 To 6
'Copy the sheet with index Y
Sheets(Y).Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
'If Y is 1 then you are copying the Header sheet so set the
value of C1
If Y = 1 Then
Sheets(ActiveWorkbook.Sheets.Count).Range("C1").Va lue = _
Sheets(1).Range("F" & 36 + 1).Value
End If
Next Y
Next X
End If
End Sub

HTH

Regards

Andy W


"thesaxonuk" wrote:

Worksheet 1 tab name "Header" Info in Cell G1 = "Master" Cell C27 has Count
of sub refs, Cell F37 has first sub ref
Worksheet 2 tab name Summary
Worksheet 3 tab name Quality
Worksheet 4 tab name Detail 1
Worksheet 5 tab name Detail 2
Worksheet 6 tab name Variance

All need to be copied when macro runs to create

Worksheet 1 to 6 as a new set of sheets auto tab name "Header 2" etc is okay
copy from Header F37 into Header 2 C1 then for the next group of sheets i.e.
Header3 copy Header F38 into Header 3 C1 and so on until finished.

"Andy Williams" wrote:

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

Sorry, there was an error in my last post the code should read:-

Sub CopySheets()
Dim X As Integer
Dim Y As Integer
'Test to see if Header sheet cell G1 = Master
If Sheets(1).Range("G1").Value = "Master" Then
'Set the number of Repeats by getting the value of Header Sheet Cell
B34
For X = 1 To Sheets(1).Range("B34").Value
'Set a repeat for each of the 6 original sheets
For Y = 1 To 6
'Copy the sheet with index Y
Sheets(Y).Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
'If Y is 1 then you are copying the Header sheet so set the
value of C1
If Y = 1 Then
Sheets(ActiveWorkbook.Sheets.Count).Range("C1").Va lue = _
Sheets(1).Range("F" & 36 + X).Value
End If
Next Y
Next X
End If
End Sub

"thesaxonuk" wrote:

Worksheet 1 tab name "Header" Info in Cell G1 = "Master" Cell C27 has Count
of sub refs, Cell F37 has first sub ref
Worksheet 2 tab name Summary
Worksheet 3 tab name Quality
Worksheet 4 tab name Detail 1
Worksheet 5 tab name Detail 2
Worksheet 6 tab name Variance

All need to be copied when macro runs to create

Worksheet 1 to 6 as a new set of sheets auto tab name "Header 2" etc is okay
copy from Header F37 into Header 2 C1 then for the next group of sheets i.e.
Header3 copy Header F38 into Header 3 C1 and so on until finished.

"Andy Williams" wrote:

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

Thanks works great bar one bit.
The code copies F37 the first time but then does not copy F38 for the second
set of sheets and so on.

"Andy Williams" wrote:

Sorry, there was an error in my last post the code should read:-

Sub CopySheets()
Dim X As Integer
Dim Y As Integer
'Test to see if Header sheet cell G1 = Master
If Sheets(1).Range("G1").Value = "Master" Then
'Set the number of Repeats by getting the value of Header Sheet Cell
B34
For X = 1 To Sheets(1).Range("B34").Value
'Set a repeat for each of the 6 original sheets
For Y = 1 To 6
'Copy the sheet with index Y
Sheets(Y).Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
'If Y is 1 then you are copying the Header sheet so set the
value of C1
If Y = 1 Then
Sheets(ActiveWorkbook.Sheets.Count).Range("C1").Va lue = _
Sheets(1).Range("F" & 36 + X).Value
End If
Next Y
Next X
End If
End Sub

"thesaxonuk" wrote:

Worksheet 1 tab name "Header" Info in Cell G1 = "Master" Cell C27 has Count
of sub refs, Cell F37 has first sub ref
Worksheet 2 tab name Summary
Worksheet 3 tab name Quality
Worksheet 4 tab name Detail 1
Worksheet 5 tab name Detail 2
Worksheet 6 tab name Variance

All need to be copied when macro runs to create

Worksheet 1 to 6 as a new set of sheets auto tab name "Header 2" etc is okay
copy from Header F37 into Header 2 C1 then for the next group of sheets i.e.
Header3 copy Header F38 into Header 3 C1 and so on until finished.

"Andy Williams" wrote:

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

Ok, can you tell what you've got in Cell B34 on the Header sheet and list the
values in F37, F38, F39 etc. I'll try and reproduce it in my copy.

Andy W

"thesaxonuk" wrote:

Thanks works great bar one bit.
The code copies F37 the first time but then does not copy F38 for the second
set of sheets and so on.

"Andy Williams" wrote:

Sorry, there was an error in my last post the code should read:-

Sub CopySheets()
Dim X As Integer
Dim Y As Integer
'Test to see if Header sheet cell G1 = Master
If Sheets(1).Range("G1").Value = "Master" Then
'Set the number of Repeats by getting the value of Header Sheet Cell
B34
For X = 1 To Sheets(1).Range("B34").Value
'Set a repeat for each of the 6 original sheets
For Y = 1 To 6
'Copy the sheet with index Y
Sheets(Y).Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
'If Y is 1 then you are copying the Header sheet so set the
value of C1
If Y = 1 Then
Sheets(ActiveWorkbook.Sheets.Count).Range("C1").Va lue = _
Sheets(1).Range("F" & 36 + X).Value
End If
Next Y
Next X
End If
End Sub

"thesaxonuk" wrote:

Worksheet 1 tab name "Header" Info in Cell G1 = "Master" Cell C27 has Count
of sub refs, Cell F37 has first sub ref
Worksheet 2 tab name Summary
Worksheet 3 tab name Quality
Worksheet 4 tab name Detail 1
Worksheet 5 tab name Detail 2
Worksheet 6 tab name Variance

All need to be copied when macro runs to create

Worksheet 1 to 6 as a new set of sheets auto tab name "Header 2" etc is okay
copy from Header F37 into Header 2 C1 then for the next group of sheets i.e.
Header3 copy Header F38 into Header 3 C1 and so on until finished.

"Andy Williams" wrote:

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

B34 = 8 This figure is a variable and can range form 0 to 25

F37 = 00003abc
F38 = 00003def
F39 = 00006abc
F40 = 00007abc

"Andy Williams" wrote:

Ok, can you tell what you've got in Cell B34 on the Header sheet and list the
values in F37, F38, F39 etc. I'll try and reproduce it in my copy.

Andy W

"thesaxonuk" wrote:

Thanks works great bar one bit.
The code copies F37 the first time but then does not copy F38 for the second
set of sheets and so on.

"Andy Williams" wrote:

Sorry, there was an error in my last post the code should read:-

Sub CopySheets()
Dim X As Integer
Dim Y As Integer
'Test to see if Header sheet cell G1 = Master
If Sheets(1).Range("G1").Value = "Master" Then
'Set the number of Repeats by getting the value of Header Sheet Cell
B34
For X = 1 To Sheets(1).Range("B34").Value
'Set a repeat for each of the 6 original sheets
For Y = 1 To 6
'Copy the sheet with index Y
Sheets(Y).Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
'If Y is 1 then you are copying the Header sheet so set the
value of C1
If Y = 1 Then
Sheets(ActiveWorkbook.Sheets.Count).Range("C1").Va lue = _
Sheets(1).Range("F" & 36 + X).Value
End If
Next Y
Next X
End If
End Sub

"thesaxonuk" wrote:

Worksheet 1 tab name "Header" Info in Cell G1 = "Master" Cell C27 has Count
of sub refs, Cell F37 has first sub ref
Worksheet 2 tab name Summary
Worksheet 3 tab name Quality
Worksheet 4 tab name Detail 1
Worksheet 5 tab name Detail 2
Worksheet 6 tab name Variance

All need to be copied when macro runs to create

Worksheet 1 to 6 as a new set of sheets auto tab name "Header 2" etc is okay
copy from Header F37 into Header 2 C1 then for the next group of sheets i.e.
Header3 copy Header F38 into Header 3 C1 and so on until finished.

"Andy Williams" wrote:

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

Ok, presumably you've also got figures in cells F41-F44 to cater for the
other 4 loops (8-4)?

I've tried it on my version and it works.

I get:-

Header(2) Cell C1 = 00003abc
Header(3) Cell C1 = 00003def
Header(4) Cell C1 = 00006abc

How are you calling the subroutine?

Andy W

"thesaxonuk" wrote:

B34 = 8 This figure is a variable and can range form 0 to 25

F37 = 00003abc
F38 = 00003def
F39 = 00006abc
F40 = 00007abc

"Andy Williams" wrote:

Ok, can you tell what you've got in Cell B34 on the Header sheet and list the
values in F37, F38, F39 etc. I'll try and reproduce it in my copy.

Andy W

"thesaxonuk" wrote:

Thanks works great bar one bit.
The code copies F37 the first time but then does not copy F38 for the second
set of sheets and so on.

"Andy Williams" wrote:

Sorry, there was an error in my last post the code should read:-

Sub CopySheets()
Dim X As Integer
Dim Y As Integer
'Test to see if Header sheet cell G1 = Master
If Sheets(1).Range("G1").Value = "Master" Then
'Set the number of Repeats by getting the value of Header Sheet Cell
B34
For X = 1 To Sheets(1).Range("B34").Value
'Set a repeat for each of the 6 original sheets
For Y = 1 To 6
'Copy the sheet with index Y
Sheets(Y).Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
'If Y is 1 then you are copying the Header sheet so set the
value of C1
If Y = 1 Then
Sheets(ActiveWorkbook.Sheets.Count).Range("C1").Va lue = _
Sheets(1).Range("F" & 36 + X).Value
End If
Next Y
Next X
End If
End Sub

"thesaxonuk" wrote:

Worksheet 1 tab name "Header" Info in Cell G1 = "Master" Cell C27 has Count
of sub refs, Cell F37 has first sub ref
Worksheet 2 tab name Summary
Worksheet 3 tab name Quality
Worksheet 4 tab name Detail 1
Worksheet 5 tab name Detail 2
Worksheet 6 tab name Variance

All need to be copied when macro runs to create

Worksheet 1 to 6 as a new set of sheets auto tab name "Header 2" etc is okay
copy from Header F37 into Header 2 C1 then for the next group of sheets i.e.
Header3 copy Header F38 into Header 3 C1 and so on until finished.

"Andy Williams" wrote:

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.

Loop Macro a variable number of times
 

Andy,

Currently I am running the sub from VBA but ultimately I am looking for it
to be a message that pops up saying this risk is a master do you want to run
Sub Reference program. and use a yes no cancel box.

Should i Place the code somewhere specific?

I don't imagine it is where the current active cell is.

"Andy Williams" wrote:

Ok, presumably you've also got figures in cells F41-F44 to cater for the
other 4 loops (8-4)?

I've tried it on my version and it works.

I get:-

Header(2) Cell C1 = 00003abc
Header(3) Cell C1 = 00003def
Header(4) Cell C1 = 00006abc

How are you calling the subroutine?

Andy W

"thesaxonuk" wrote:

B34 = 8 This figure is a variable and can range form 0 to 25

F37 = 00003abc
F38 = 00003def
F39 = 00006abc
F40 = 00007abc

"Andy Williams" wrote:

Ok, can you tell what you've got in Cell B34 on the Header sheet and list the
values in F37, F38, F39 etc. I'll try and reproduce it in my copy.

Andy W

"thesaxonuk" wrote:

Thanks works great bar one bit.
The code copies F37 the first time but then does not copy F38 for the second
set of sheets and so on.

"Andy Williams" wrote:

Sorry, there was an error in my last post the code should read:-

Sub CopySheets()
Dim X As Integer
Dim Y As Integer
'Test to see if Header sheet cell G1 = Master
If Sheets(1).Range("G1").Value = "Master" Then
'Set the number of Repeats by getting the value of Header Sheet Cell
B34
For X = 1 To Sheets(1).Range("B34").Value
'Set a repeat for each of the 6 original sheets
For Y = 1 To 6
'Copy the sheet with index Y
Sheets(Y).Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
'If Y is 1 then you are copying the Header sheet so set the
value of C1
If Y = 1 Then
Sheets(ActiveWorkbook.Sheets.Count).Range("C1").Va lue = _
Sheets(1).Range("F" & 36 + X).Value
End If
Next Y
Next X
End If
End Sub

"thesaxonuk" wrote:

Worksheet 1 tab name "Header" Info in Cell G1 = "Master" Cell C27 has Count
of sub refs, Cell F37 has first sub ref
Worksheet 2 tab name Summary
Worksheet 3 tab name Quality
Worksheet 4 tab name Detail 1
Worksheet 5 tab name Detail 2
Worksheet 6 tab name Variance

All need to be copied when macro runs to create

Worksheet 1 to 6 as a new set of sheets auto tab name "Header 2" etc is okay
copy from Header F37 into Header 2 C1 then for the next group of sheets i.e.
Header3 copy Header F38 into Header 3 C1 and so on until finished.

"Andy Williams" wrote:

I'm afraid you've lost me slightly.

I think I haven't understood you're original workbook structure. Could you
tell me what Sheets you have in your Workbook to start off with, which of
them you consider to be the 'Lead' sheet and which of them you wish to copy
(a variable number of times) when the value of cell G1 on the 'Lead' sheet is
"Master".

Regards

Andy W

"thesaxonuk" wrote:

Apologies I did not make myself clear replace lead with "Header" so the
"Header" sheet has the key info.

Further refinement, when you copy a sheet the tab name is added to with a
(2) or (3) after the name and so on. This is fine, what I am looking for is
from the original "Header" sheet to auto populate cell C1 on "Header (2)"
with info from cell F37 and then for "Header (3)" to be populated from cell
F38 of the original "Header" sheet until the count is reached and cell F.. is
#NA

"Andy Williams" wrote:

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.