[VBA]Adding a new sheet at the end of existing sheets-VBForums
Results 1 to 3 of 3

Thread: [VBA]Adding a new sheet at the end of existing sheets

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2007
    Location
    Oregon
    Posts
    2

    [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 01:54 PM. Reason: Clarify title

  2. #2
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    590

    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'.

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2007
    Location
    Oregon
    Posts
    2

    Re: [VBA]Adding a new sheet at the end of existing sheets

    That fixed it. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.