Programatically adding worksheets to a spreadsheet

  • Thread starter Thread starter Scott Lyon
  • Start date Start date
S

Scott Lyon

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
 
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(xlCellTypeConstants)
Set sh = Sheets.Add(Type:="c:\Sheet.xlt")
On Error Resume Next
sh.Name = cell.Value
On Error GoTo 0
Next
End Sub
 
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
 
Back
Top