I'm not sure if I can do this, as I tried doing the "record a macro" trick,
and then trying it manually, and checking what recorded in the macro... Only
to find that the macro was pretty much empty.


In a nutshell, what I've got is an Excel spreadsheet that has a bunch of
worksheets already. I want to write a macro/subroutine/function that I can
call, that will check one of the current worksheets, and for each cell (with
data) on a given line, will add a worksheet (copied from a "template"
worksheet), setting the worksheet name (both the tab and in the properties)
to the name of the selected cell.


Ideally, I'd like to have it also do a "search & replace" on the VBA code in
the new worksheet (based on the template), to adjust things to match the
file. But I can do that by hand if necessary.


Is any of this going to be possible to automate? Or am I stuck with tons of
clicking?


(Basically, I need to add a few hundred worksheets in this fashion - now you
know why I'd rather automate the process)



Thanks!
-Scott

Oops, I take it back... I just tried recording the macro again, and it
seemed to work (partially). The macro recorded (properly) creating the new
worksheet, and renaming the sheet. But it didn't record me changing any of
the VBA code (the search & replace I mentioned before).


Is there any way to automate that step?


Thanks!
-Scott

"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
I'm not sure if I can do this, as I tried doing the "record a macro"
trick,
and then trying it manually, and checking what recorded in the macro...
Only
to find that the macro was pretty much empty.


In a nutshell, what I've got is an Excel spreadsheet that has a bunch of
worksheets already. I want to write a macro/subroutine/function that I can
call, that will check one of the current worksheets, and for each cell
(with
data) on a given line, will add a worksheet (copied from a "template"
worksheet), setting the worksheet name (both the tab and in the
properties)
to the name of the selected cell.


Ideally, I'd like to have it also do a "search & replace" on the VBA code
in
the new worksheet (based on the template), to adjust things to match the
file. But I can do that by hand if necessary.


Is any of this going to be possible to automate? Or am I stuck with tons
of
clicking?


(Basically, I need to add a few hundred worksheets in this fashion - now
you
know why I'd rather automate the process)



Thanks!
-Scott

I don't think you can change the code name off a sheet with vba??
The help say <You cannot programmatically change this property at run time.

Try this with the names in row 1 off Sheet1
It will ad a sheet template named c:\Sheet.xlt

Sub test()
Dim cell As Range
Dim sh As Worksheet
For Each cell In Sheets("Sheet1").Rows(1).Cells.SpecialCells(xlCell TypeConstants)
Set sh = Sheets.Add(Type:="c:\Sheet.xlt")
On Error Resume Next
sh.Name = cell.Value
On Error GoTo 0
Next
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ...
I'm not sure if I can do this, as I tried doing the "record a macro" trick,
and then trying it manually, and checking what recorded in the macro... Only
to find that the macro was pretty much empty.


In a nutshell, what I've got is an Excel spreadsheet that has a bunch of
worksheets already. I want to write a macro/subroutine/function that I can
call, that will check one of the current worksheets, and for each cell (with
data) on a given line, will add a worksheet (copied from a "template"
worksheet), setting the worksheet name (both the tab and in the properties)
to the name of the selected cell.


Ideally, I'd like to have it also do a "search & replace" on the VBA code in
the new worksheet (based on the template), to adjust things to match the
file. But I can do that by hand if necessary.


Is any of this going to be possible to automate? Or am I stuck with tons of
clicking?


(Basically, I need to add a few hundred worksheets in this fashion - now you
know why I'd rather automate the process)



Thanks!
-Scott

No sure what you intend to do with the code in the sheet, but you can look
at Chip Pearson's page on working in the VBE with code.

http://www.cpearson.com/excel/vbe.htm

The macro recorder isn't going to record things you do in the VBE.

Regards,
Tom Ogilvy


"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
Oops, I take it back... I just tried recording the macro again, and it
seemed to work (partially). The macro recorded (properly) creating the new
worksheet, and renaming the sheet. But it didn't record me changing any of
the VBA code (the search & replace I mentioned before).


Is there any way to automate that step?


Thanks!
-Scott

"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
I'm not sure if I can do this, as I tried doing the "record a macro"
trick,
and then trying it manually, and checking what recorded in the macro...
Only
to find that the macro was pretty much empty.


In a nutshell, what I've got is an Excel spreadsheet that has a bunch of
worksheets already. I want to write a macro/subroutine/function that I
can
call, that will check one of the current worksheets, and for each cell
(with
data) on a given line, will add a worksheet (copied from a "template"
worksheet), setting the worksheet name (both the tab and in the
properties)
to the name of the selected cell.


Ideally, I'd like to have it also do a "search & replace" on the VBA
code
in
the new worksheet (based on the template), to adjust things to match the
file. But I can do that by hand if necessary.


Is any of this going to be possible to automate? Or am I stuck with tons
of
clicking?


(Basically, I need to add a few hundred worksheets in this fashion - now
you
know why I'd rather automate the process)



Thanks!
-Scott

What I wanted to do was a simple search and replace on the code itself in
the "new" version (copied from the template).

For example, in the template, I have a line that looks like:

strFileName = "FILE.txt"

and elsewhere there is another line that says:

strCategory = "ABC"


What I want to do, is look in the reference worksheet, and for each cell in
a given row, copy the template worksheet, rename it, and then modify the
code to do a replace of the text FILE with the data from the cell, and to
replace the text ABC with a constant (depending on which row I am reading
the data from on the reference worksheet).


I don't mind if I have to just hard-code the macro to run for just one row,
and then change it to run for another row. I'm only dealing with around a
dozen rows. The thing is, some rows have as many as 200 columns, which is
why I need to automate the thing where possible.


-Scott

"Tom Ogilvy" wrote in message
...
No sure what you intend to do with the code in the sheet, but you can look
at Chip Pearson's page on working in the VBE with code.

http://www.cpearson.com/excel/vbe.htm

The macro recorder isn't going to record things you do in the VBE.

Regards,
Tom Ogilvy


"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
Oops, I take it back... I just tried recording the macro again, and it
seemed to work (partially). The macro recorded (properly) creating the
new
worksheet, and renaming the sheet. But it didn't record me changing any
of
the VBA code (the search & replace I mentioned before).


Is there any way to automate that step?


Thanks!
-Scott

"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
I'm not sure if I can do this, as I tried doing the "record a macro"
trick,
and then trying it manually, and checking what recorded in the
macro...
Only
to find that the macro was pretty much empty.


In a nutshell, what I've got is an Excel spreadsheet that has a bunch
of
worksheets already. I want to write a macro/subroutine/function that I
can
call, that will check one of the current worksheets, and for each cell
(with
data) on a given line, will add a worksheet (copied from a "template"
worksheet), setting the worksheet name (both the tab and in the
properties)
to the name of the selected cell.


Ideally, I'd like to have it also do a "search & replace" on the VBA
code
in
the new worksheet (based on the template), to adjust things to match
the
file. But I can do that by hand if necessary.


Is any of this going to be possible to automate? Or am I stuck with
tons
of
clicking?


(Basically, I need to add a few hundred worksheets in this fashion -
now
you
know why I'd rather automate the process)



Thanks!
-Scott