Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB to add worksheet to end of workbook (2000/2003)

    I need to add a worksheet at the end of the workbook. I have the following comand to add the sheet. What is the VB command to have it insert the last sheet at the end. Thanks.

    With worksheets.Add
    .range ("a1") = "Date"
    end with

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB to add worksheet to end of workbook (2000/2003)

    Hi,
    Use something like:
    set sht = sheets.add(After:=sheets(sheets.count))
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to add worksheet to end of workbook (2000/2003)

    Thanks Rori, However this comands adds an extra worksheet. It does add the worksheet I want to at the end, but I also get an extra sheet. Here is the VB command. Any ideas where I went wrong?
    Sub InsertSheet()
    Dim wk As Worksheet
    On Error Resume Next
    Set wk = worksheets("allotment")

    'If no error occurred, then worksheet already exists
    If Err = 0 Then Exit Sub

    'if error exist then go to 0 and exit sub
    On Error GoTo 0

    Set sht = Sheets.Add(After:=Sheets(Sheets.Count))

    'If spreadsheet is built do the following
    With worksheets.Add
    .Name = "allotment"
    .Range("a6") = "Date"
    End with
    End sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB to add worksheet to end of workbook (2000/2003)

    You now have 2 Add statements, so you get 2 new sheets.

    Change

    Set sht = Sheets.Add(After:=Sheets(Sheets.Count))

    'If spreadsheet is built do the following
    With worksheets.Add
    .Name = "allotment"
    .Range("a6") = "Date"
    End with

    to

    With Sheets.Add(After:=Sheets(Sheets.Count))
    .Name = "allotment"
    .Range("A6") = "Date"
    End With

  5. #5
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to add worksheet to end of workbook (2000/2003)

    Thanks Hans.

Posting Permissions

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