Name worksheets dependant on Cell entries

[ewan7279]

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

 

[Markus Scheible]

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

 

[ewan7279]

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]

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

 

[ewan7279]

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

 

[ewan7279]

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.

 

[Markus Scheible]

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

 

[Markus Scheible]

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

 

[ewan7279]

Thanks Markus

Hi Ewan,



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.



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...


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


Best

Markus

 

[Markus Scheible]

Good morning Ewan,

you're wellcome ;o)

Best

Markus