programatically create new worksheet

C

Chris

to all you experts out there, I need some help with creating a macro.

I have 2 workbooks.
workbook 1 has one sheet with a template
workbook 2 has one sheet with about 200 rows

I would like to do 2 things here.
-automatically create new worksheets and have them named based on the
data in column C from workbook 2 while at the same time use the
template from workbook 1 to be included in the new named sheets.

I also have data in Columns D, E , and F in workbook 2 that I also
need created along with the new named sheets, and created in column A
rows 1, 2, and 3 respective to the newly named worksheets.

Any idea on how to go about doing this or am I not explaining this
clear enough?

Any help is greatly appreciated.

Thanks,

Chris
 
J

Joel

The code below copies the entire worksheet from the templet (not just
columns D:F) and places new worksheet in the workbook that contains the sheet
names. The macro is designed to be placed in the workbook with the sheet
names. The code will open the templet file at the start and then clos it at
the end. Change the Folder name and templet filename as necessary.


Sub newsheets()

Const Folder = "C:\temp\test\"
Const TempletName = "Book1.xls"

Workbooks.Open Filename:=Folder & TempletName
Set templetbk = ActiveWorkbook
Set templetsht = templetbk.ActiveSheet

With ThisWorkbook
Set sht1 = .ActiveSheet
RowCount = 1
Do While sht1.Range("A" & RowCount) <> ""

templetsht.Copy _
after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = sht1.Range("A" & RowCount)

RowCount = RowCount + 1
Loop
End With

templetbk.Close
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top