Hi,

I am trying (and failing) to create a macro in a master document that will
copy a template sreadsheet 'n' number of times where 'n' is determined by the
users' entries (countif non-blank cells of cost centre names column) in a
setup sheet (there is a maximum of 50 cost centres that can be entered). I
would then like to name each spreadsheet from the users' entries.

The user also has to enter codes (up to a max of 25) that are specific to
the cost centres into the columns adjacent to the cost centre name on the
setup sheet. I would like to copy and transpose these into column W of each
of the cost centre sheets where they are used as a lookup table.

I would then like the template sheet to become a total sheet with a simple
sum of the first to last sheets inclusive, but I cannot figure how to do this
when the number of cost centres (and their names) will change dependant upon
who enters the data.

Any help will be gratefully received.

Ewan

Hi Ewan,

I am trying (and failing) to create a macro in a master
document that will
copy a template sreadsheet 'n' number of times where 'n'
is determined by the
users' entries (countif non-blank cells of cost centre
names column) in a
setup sheet (there is a maximum of 50 cost centres that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names in
column "names".


Best

Markus

Markus,

Thanks for the info, but I now have the following (thanks in the main to a
previous Tom Ogilvy post) which now works to copy and rename the template:

Sub TEMPLATE_COPY

Dim cell As Range, Rng As Range
With Worksheets("SETUP SHEET")
Set Rng = .Range ( .Range("C3:C52"), .Range("C3:C52").End(xlDown))
End With
For Each Cell In Rng
If Cell < "" Then
Sheets("TEMPLATE").Copy AFTER := Sheets(Sheets.Count)
ActiveSheet.Name = Cell.Value
End If
Next
End Sub

I'm now trying to copy and transpose the row of codes adjacent to the cost
centre name in the setup sheet into each of the cost centre sheets column 'W'
if you can help.

Any ideas?

"Markus Scheible" wrote:

Hi Ewan,

I am trying (and failing) to create a macro in a master
document that will
copy a template sreadsheet 'n' number of times where 'n'
is determined by the
users' entries (countif non-blank cells of cost centre
names column) in a
setup sheet (there is a maximum of 50 cost centres that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names in
column "names".


Best

Markus

Hi Ewan,


Sub TEMPLATE_COPY

Dim cell As Range, Rng As Range
With Worksheets("SETUP SHEET")
Set Rng = .Range ( .Range("C3:C52"), .Range
("C3:C52").End(xlDown))
End With
For Each Cell In Rng
If Cell < "" Then
Sheets("TEMPLATE").Copy AFTER := Sheets
(Sheets.Count)
ActiveSheet.Name = Cell.Value

Worksheets("SETUP SHEET").Range("C"& Cell.Row).Copy
ActiveSheet.Range("W1").PasteSpecial (Transpose = True)

should do it... Cell.Row gives the Row Number...

Best

Markus



End If
Next
End Sub

I'm now trying to copy and transpose the row of codes
adjacent to the cost
centre name in the setup sheet into each of the cost
centre sheets column 'W'
if you can help.

Any ideas?

"Markus Scheible" wrote:

Hi Ewan,

I am trying (and failing) to create a macro in a
master
document that will
copy a template sreadsheet 'n' number of times
where 'n'
is determined by the
users' entries (countif non-blank cells of cost centre
names column) in a
setup sheet (there is a maximum of 50 cost centres
that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names in
column "names".


Best

Markus

.

Hi Markus,

Unfortunately, this has not worked. For some reason, the macro only creates
the first sheet in the list now with this additional code.

I commented out the 2nd line 'ActiveSheet.Range...' and it worked fully
again, but obviously without pasting the information anywhere.

I altered it slightly to 'Selection.PasteSpecial' and it still does not work.

Additionally, the info I want to copy is in columns D to M, so would I enter
Range(" D:M "& Cell.Row).Copy?

Thanks

"Markus Scheible" wrote:

Hi Ewan,


Sub TEMPLATE_COPY

Dim cell As Range, Rng As Range
With Worksheets("SETUP SHEET")
Set Rng = .Range ( .Range("C3:C52"), .Range
("C3:C52").End(xlDown))
End With
For Each Cell In Rng
If Cell < "" Then
Sheets("TEMPLATE").Copy AFTER := Sheets
(Sheets.Count)
ActiveSheet.Name = Cell.Value

Worksheets("SETUP SHEET").Range("C"& Cell.Row).Copy
ActiveSheet.Range("W1").PasteSpecial (Transpose = True)

should do it... Cell.Row gives the Row Number...

Best

Markus



End If
Next
End Sub

I'm now trying to copy and transpose the row of codes
adjacent to the cost
centre name in the setup sheet into each of the cost
centre sheets column 'W'
if you can help.

Any ideas?

"Markus Scheible" wrote:

Hi Ewan,

I am trying (and failing) to create a macro in a
master
document that will
copy a template sreadsheet 'n' number of times
where 'n'
is determined by the
users' entries (countif non-blank cells of cost centre
names column) in a
setup sheet (there is a maximum of 50 cost centres
that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names in
column "names".


Best

Markus

.

Markus,

I've worked it out now, apart from how to enter the range D:M and enter the
total sum formula in the template sheet after all others have been created.

Thanks,
Ewan.

"ewan7279" wrote:

Hi Markus,

Unfortunately, this has not worked. For some reason, the macro only creates
the first sheet in the list now with this additional code.

I commented out the 2nd line 'ActiveSheet.Range...' and it worked fully
again, but obviously without pasting the information anywhere.

I altered it slightly to 'Selection.PasteSpecial' and it still does not work.

Additionally, the info I want to copy is in columns D to M, so would I enter
Range(" D:M "& Cell.Row).Copy?

Thanks

"Markus Scheible" wrote:

Hi Ewan,


Sub TEMPLATE_COPY

Dim cell As Range, Rng As Range
With Worksheets("SETUP SHEET")
Set Rng = .Range ( .Range("C3:C52"), .Range
("C3:C52").End(xlDown))
End With
For Each Cell In Rng
If Cell < "" Then
Sheets("TEMPLATE").Copy AFTER := Sheets
(Sheets.Count)
ActiveSheet.Name = Cell.Value

Worksheets("SETUP SHEET").Range("C"& Cell.Row).Copy
ActiveSheet.Range("W1").PasteSpecial (Transpose = True)

should do it... Cell.Row gives the Row Number...

Best

Markus



End If
Next
End Sub

I'm now trying to copy and transpose the row of codes
adjacent to the cost
centre name in the setup sheet into each of the cost
centre sheets column 'W'
if you can help.

Any ideas?

"Markus Scheible" wrote:

Hi Ewan,

I am trying (and failing) to create a macro in a
master
document that will
copy a template sreadsheet 'n' number of times
where 'n'
is determined by the
users' entries (countif non-blank cells of cost centre
names column) in a
setup sheet (there is a maximum of 50 cost centres
that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names in
column "names".


Best

Markus

.

Hi Ewan,


Additionally, the info I want to copy is in columns D to
M, so would I enter
Range(" D:M "& Cell.Row).Copy?


You would use

Range("D" & Cell.Row, "M" & Cell.Row).Copy

because normally you would write Range("D4","M4") and
instead of four, Excel sets the row number of each cell.



Unfortunately, this has not worked. For some reason, the
macro only creates
the first sheet in the list now with this additional code.

I commented out the 2nd line 'ActiveSheet.Range...' and
it worked fully
again, but obviously without pasting the information
anywhere.

I think this is caused due to the fact that the Active
statements are very clumsy in Excel. Try using

Workbooks("abcde.xls")

instead of ActiveWorkbook - I propose this would work
then...


I altered it slightly to 'Selection.PasteSpecial' and it
still does not work.


This is nearly the same - try avoiding Select and Activate
statements and address the workbooks directly instead...


Best

Markus


Thanks

"Markus Scheible" wrote:

Hi Ewan,


Sub TEMPLATE_COPY

Dim cell As Range, Rng As Range
With Worksheets("SETUP SHEET")
Set Rng = .Range ( .Range("C3:C52"), .Range
("C3:C52").End(xlDown))
End With
For Each Cell In Rng
If Cell < "" Then
Sheets("TEMPLATE").Copy AFTER := Sheets
(Sheets.Count)
ActiveSheet.Name = Cell.Value

Worksheets("SETUP SHEET").Range("C"& Cell.Row).Copy
ActiveSheet.Range("W1").PasteSpecial (Transpose = True)

should do it... Cell.Row gives the Row Number...

Best

Markus



End If
Next
End Sub

I'm now trying to copy and transpose the row of codes
adjacent to the cost
centre name in the setup sheet into each of the cost
centre sheets column 'W'
if you can help.

Any ideas?

"Markus Scheible" wrote:

Hi Ewan,

I am trying (and failing) to create a macro in a
master
document that will
copy a template sreadsheet 'n' number of times
where 'n'
is determined by the
users' entries (countif non-blank cells of cost
centre
names column) in a
setup sheet (there is a maximum of 50 cost centres
that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names in
column "names".


Best

Markus

.


.

Hi Ewan,


I've worked it out now, apart from how to enter the range
D:M and enter the
total sum formula in the template sheet after all others
have been created.


for the range address see my other post; for the formula:

you can use Range("A1").Formula = "=Sum(...)"

to create a formula...

Best

Markus



Thanks,
Ewan.

"ewan7279" wrote:

Hi Markus,

Unfortunately, this has not worked. For some reason,
the macro only creates
the first sheet in the list now with this additional
code.

I commented out the 2nd line 'ActiveSheet.Range...' and
it worked fully
again, but obviously without pasting the information
anywhere.

I altered it slightly to 'Selection.PasteSpecial' and
it still does not work.

Additionally, the info I want to copy is in columns D
to M, so would I enter
Range(" D:M "& Cell.Row).Copy?

Thanks

"Markus Scheible" wrote:

Hi Ewan,


Sub TEMPLATE_COPY

Dim cell As Range, Rng As Range
With Worksheets("SETUP SHEET")
Set Rng = .Range ( .Range("C3:C52"), .Range
("C3:C52").End(xlDown))
End With
For Each Cell In Rng
If Cell < "" Then
Sheets("TEMPLATE").Copy AFTER := Sheets
(Sheets.Count)
ActiveSheet.Name = Cell.Value

Worksheets("SETUP SHEET").Range("C"& Cell.Row).Copy
ActiveSheet.Range("W1").PasteSpecial (Transpose =
True)

should do it... Cell.Row gives the Row Number...

Best

Markus



End If
Next
End Sub

I'm now trying to copy and transpose the row of
codes
adjacent to the cost
centre name in the setup sheet into each of the cost
centre sheets column 'W'
if you can help.

Any ideas?

"Markus Scheible" wrote:

Hi Ewan,

I am trying (and failing) to create a macro in a
master
document that will
copy a template sreadsheet 'n' number of times
where 'n'
is determined by the
users' entries (countif non-blank cells of cost
centre
names column) in a
setup sheet (there is a maximum of 50 cost
centres
that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset
(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names
in
column "names".


Best

Markus

.


.

Thanks Markus

"Markus Scheible" wrote:

Hi Ewan,


Additionally, the info I want to copy is in columns D to
M, so would I enter
Range(" D:M "& Cell.Row).Copy?


You would use

Range("D" & Cell.Row, "M" & Cell.Row).Copy

because normally you would write Range("D4","M4") and
instead of four, Excel sets the row number of each cell.



Unfortunately, this has not worked. For some reason, the
macro only creates
the first sheet in the list now with this additional code.

I commented out the 2nd line 'ActiveSheet.Range...' and
it worked fully
again, but obviously without pasting the information
anywhere.

I think this is caused due to the fact that the Active
statements are very clumsy in Excel. Try using

Workbooks("abcde.xls")

instead of ActiveWorkbook - I propose this would work
then...


I altered it slightly to 'Selection.PasteSpecial' and it
still does not work.


This is nearly the same - try avoiding Select and Activate
statements and address the workbooks directly instead...


Best

Markus


Thanks

"Markus Scheible" wrote:

Hi Ewan,


Sub TEMPLATE_COPY

Dim cell As Range, Rng As Range
With Worksheets("SETUP SHEET")
Set Rng = .Range ( .Range("C3:C52"), .Range
("C3:C52").End(xlDown))
End With
For Each Cell In Rng
If Cell < "" Then
Sheets("TEMPLATE").Copy AFTER := Sheets
(Sheets.Count)
ActiveSheet.Name = Cell.Value

Worksheets("SETUP SHEET").Range("C"& Cell.Row).Copy
ActiveSheet.Range("W1").PasteSpecial (Transpose = True)

should do it... Cell.Row gives the Row Number...

Best

Markus



End If
Next
End Sub

I'm now trying to copy and transpose the row of codes
adjacent to the cost
centre name in the setup sheet into each of the cost
centre sheets column 'W'
if you can help.

Any ideas?

"Markus Scheible" wrote:

Hi Ewan,

I am trying (and failing) to create a macro in a
master
document that will
copy a template sreadsheet 'n' number of times
where 'n'
is determined by the
users' entries (countif non-blank cells of cost
centre
names column) in a
setup sheet (there is a maximum of 50 cost centres
that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names in
column "names".


Best

Markus

.


.

Good morning Ewan,

you're wellcome ;o)

Best

Markus


-----Original Message-----
Thanks Markus

"Markus Scheible" wrote:

Hi Ewan,


Additionally, the info I want to copy is in columns D
to
M, so would I enter
Range(" D:M "& Cell.Row).Copy?


You would use

Range("D" & Cell.Row, "M" & Cell.Row).Copy

because normally you would write Range("D4","M4") and
instead of four, Excel sets the row number of each cell.



Unfortunately, this has not worked. For some reason,
the
macro only creates
the first sheet in the list now with this additional
code.

I commented out the 2nd line 'ActiveSheet.Range...'
and
it worked fully
again, but obviously without pasting the information
anywhere.

I think this is caused due to the fact that the Active
statements are very clumsy in Excel. Try using

Workbooks("abcde.xls")

instead of ActiveWorkbook - I propose this would work
then...


I altered it slightly to 'Selection.PasteSpecial' and
it
still does not work.


This is nearly the same - try avoiding Select and
Activate
statements and address the workbooks directly instead...


Best

Markus


Thanks

"Markus Scheible" wrote:

Hi Ewan,


Sub TEMPLATE_COPY

Dim cell As Range, Rng As Range
With Worksheets("SETUP SHEET")
Set Rng = .Range ( .Range("C3:C52"), .Range
("C3:C52").End(xlDown))
End With
For Each Cell In Rng
If Cell < "" Then
Sheets("TEMPLATE").Copy AFTER :=
Sheets
(Sheets.Count)
ActiveSheet.Name = Cell.Value

Worksheets("SETUP SHEET").Range("C"& Cell.Row).Copy
ActiveSheet.Range("W1").PasteSpecial (Transpose =
True)

should do it... Cell.Row gives the Row Number...

Best

Markus



End If
Next
End Sub

I'm now trying to copy and transpose the row of
codes
adjacent to the cost
centre name in the setup sheet into each of the
cost
centre sheets column 'W'
if you can help.

Any ideas?

"Markus Scheible" wrote:

Hi Ewan,

I am trying (and failing) to create a macro in a
master
document that will
copy a template sreadsheet 'n' number of times
where 'n'
is determined by the
users' entries (countif non-blank cells of cost
centre
names column) in a
setup sheet (there is a maximum of 50 cost
centres
that
can be entered).

try:

m = Range("yourentrycell").Value

for i=1 to m

Workbooks("abcd.xls").Sheets.Add
Workbooks.Sheets(i).Name = Range("names").Offset
(i,
0).Value
i = i + 1

Next i


With that you create i new sheets with the names
in
column "names".


Best

Markus

.


.


.