code to automatically create new sheets

[Drew]

Hi all,
I have a VBA related question (I’m helping out a co-worker but I have no VBA experience).
The following code essentially looks at a list of companies from the “reports” sheet; puts the first name from that list in cell c15 on sheet “bpnoi”; performs an essbase extract on sheet “bpnoi” (Del range); prints results from “bpnoi”; loops through company list from the “reports” sheet.
What I would like to do is replace the printing code with code that will add new sheets. I know this is possible from what I found in prior posts, but I couldn't piece together the code.
Thanks for the help.
Drew

Sub printreport()
Sheets("bpnoi").Select
ColIndex = 3
UnitIndex = 3
Rows("11:600").Select
'Selection.RowHeight = 12.75

While Not IsEmpty(Worksheets("Reports").Cells(UnitIndex, "A"))

range("del").Select
Dim Del As range
Set Del = ThisWorkbook.Worksheets("bpnoi").range("Del")


If (Worksheets("Reports").Cells(UnitIndex, ColIndex) = 1) Then
ActiveSheet.Cells(15, "C") = "'" & Worksheets("Reports").Cells(UnitIndex, "A")
W = EssVRetrieve("bpnoi", Del, 1)
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
UnitIndex = UnitIndex + 1

Wend


End Sub

 

[gplhl]

Sheets.Add

 

[Drew]

Gary,
I placed the code in but a run-time error occurred. Of course while looking over my request I found out that I may not have explained properly...
After the program executes the essbase retrieval, I want to copy the "bpnoi" sheet to a new sheet with a name from cell C15 (or the UnitIndex).
Thanks,
Drew

 

[gplhl]

On 2002-10-22 14:11, Drew wrote:
Gary,
I placed the code in but a run-time error occurred. Of course while looking over my request I found out that I may not have explained properly...
After the program executes the essbase retrieval, I want to copy the "bpnoi" sheet to a new sheet with a name from cell C15 (or the UnitIndex).
Thanks,
Drew

NewSheetName = Range("C15").Value
Sheets("bpnoi").Copy After:=ActiveSheet
ActiveSheet.Name = NewSheetName