-
Aug 14th, 2007, 12:52 PM
#1
Thread Starter
New Member
[VBA]Adding a new sheet at the end of existing sheets
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.
Last edited by Inked; Aug 14th, 2007 at 12:54 PM.
Reason: Clarify title
-
Aug 14th, 2007, 05:57 PM
#2
Fanatic Member
Re: [VBA]Adding a new sheet at the end of existing sheets
Try this:
Code:
ThisWorkbook.Sheets.Add After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet
Incidently, you had a typo in Type:=x1Worksheet. It should be lower case 'L', not '1'.
-
Aug 15th, 2007, 10:08 AM
#3
Thread Starter
New Member
Re: [VBA]Adding a new sheet at the end of existing sheets
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|