Dynamically generate a new Excel sheet

Posted by Geoff on July 24, 2000 11:10 AM

I am trying to create a new excel sheet using VBA in the current workbook. I want to create the sheet, change its name, and add some information into it all using VBA. I have tried a couple of methods such as this one:

Dim newSheet As New Excel.Worksheet (or just Worksheet)
newSheet.Name = "Report"
newSheet.Range("A1").Value = "Test"

When it executes that code, it stops on newSheet.Name = "Report" and give the following error: "Class doesn't support Automation" which I'm not sure what that means. Any help would be great. Thanks in advance!

Posted by Ryan on July 24, 0100 12:02 PM

Hi Geoff,

Try this. You can add what you need using nwsht.Range("A1") = "..."

Sub NewSheet()
Dim nwsht As Worksheet

Set nwsht = Worksheets.Add
nwsht.Name = "Hello"
' Add more code here.
End Sub