I have been trying to get a new sheet to be created at the end of all the current sheets, but when I execute my code i get a "runtime error '1004': Method 'add' of object 'sheets' failed" The code surrounding the error is as follows:
Code:
Range("A2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'Ask If the user wants to build another ink'
Do Until Msg1 = 7
Msg1 = MsgBox("Do you want to add another ink?", vbYesNo + vbQuestion, "Another Ink?")
'If Yes'
If Msg1 = 6 Then
'Create a new sheet'
ThisWorkbook.Sheets.Add After:=Worksheets.Count, Count:=1, Type:=x1Worksheet
'Go to sheet1'
Worksheets("Sheet1").Activate
'Input the Headers'
Range("A2").Select
ActiveCell.Value = "Component"
Range("B2").Select
ActiveCell.Value = "Wt. %"
Range("C2").Select
ActiveCell.Value = "Calc. Mass(g)"
Range("D2").Select
ActiveCell.Value = "Actual Mass (g)"
Range("A1").Select
Selection.Font.Bold = True
Range("A2:D2").Select
Selection.Font.Bold = True
I am running into the problem on this line "ThisWorkbook.Sheets.Add After:=Worksheets.Count, Count:=1, Type:=x1Worksheet"
Any advise would be greatly appreciated.