G
gumby
I keep getting a subscript out of range error with this macro. Any
help would be great.
Public Sub AddSheetToEnd()
'Create a new sheet
Dim NewWorksheet As Worksheet
Set NewWorksheet = _
Application.Sheets.Add( _
After:=Worksheets(GetLastSheet), _
Type:=XlSheetType.xlWorksheet)
' Rename the worksheet
NewWorksheet.Name = "Added Worksheet"
' Place a title in the worksheet.
NewWorksheet.Cells(1, 1) = "Sample Data"
' Add some headings.
NewWorksheet.Cells(3, 1) = "Lable"
NewWorksheet.Cells(3, 2) = "Data"
NewWorksheet.Cells(3, 3) = "Sum"
' Format the title and headings.
With NewWorksheet.Range("A1", "B1")
.Font.Bold = True
.Font.Size = 12
.Borders.LineStyle = XlLineStyle.xlContinuous
.Borders.Weight = XlBorderWeight.xlThick
.Interior.Pattern = XlPattern.xlPatternAutomatic
.Interior.Color = RGB(255, 255, 0)
End With
NewWorksheet.Range("A3", "C3").Font.Bold = True
' Create some data entries.
Dim Counter As Integer
For Counter = 1 To 6
' Add some data labels.
NewWorksheet.Cells(Counter + 3, 1) = _
"Element " + CStr(Counter)
' Add Random integer value between 1 and 10.
NewWorksheet.Cells(Counter + 3, 2) = _
CInt(Rnd() * 10)
' Add an equation to the third column
If Counter = 1 Then
NewWorksheet.Cells(Count + 3, 3) = _
"=B" + CStr(Counter + 3)
Else
NewWorksheet.Cells(Counter + 3, 3) = _
"= C" + CStr(Counter + 2) + _
" + B" + CStr(Counter + 3)
End If
Next
End Sub
help would be great.
Public Sub AddSheetToEnd()
'Create a new sheet
Dim NewWorksheet As Worksheet
Set NewWorksheet = _
Application.Sheets.Add( _
After:=Worksheets(GetLastSheet), _
Type:=XlSheetType.xlWorksheet)
' Rename the worksheet
NewWorksheet.Name = "Added Worksheet"
' Place a title in the worksheet.
NewWorksheet.Cells(1, 1) = "Sample Data"
' Add some headings.
NewWorksheet.Cells(3, 1) = "Lable"
NewWorksheet.Cells(3, 2) = "Data"
NewWorksheet.Cells(3, 3) = "Sum"
' Format the title and headings.
With NewWorksheet.Range("A1", "B1")
.Font.Bold = True
.Font.Size = 12
.Borders.LineStyle = XlLineStyle.xlContinuous
.Borders.Weight = XlBorderWeight.xlThick
.Interior.Pattern = XlPattern.xlPatternAutomatic
.Interior.Color = RGB(255, 255, 0)
End With
NewWorksheet.Range("A3", "C3").Font.Bold = True
' Create some data entries.
Dim Counter As Integer
For Counter = 1 To 6
' Add some data labels.
NewWorksheet.Cells(Counter + 3, 1) = _
"Element " + CStr(Counter)
' Add Random integer value between 1 and 10.
NewWorksheet.Cells(Counter + 3, 2) = _
CInt(Rnd() * 10)
' Add an equation to the third column
If Counter = 1 Then
NewWorksheet.Cells(Count + 3, 3) = _
"=B" + CStr(Counter + 3)
Else
NewWorksheet.Cells(Counter + 3, 3) = _
"= C" + CStr(Counter + 2) + _
" + B" + CStr(Counter + 3)
End If
Next
End Sub