Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to copy a worksheet from one workbook (wkbNew) to another workbook (wkbOri) and want it to go to the end and take on the next sheet number.

    wkbNew.Worksheets(1).Copy After:=wkbOri.Sheets(13)
    wkbOri.Worksheets(14).Name = "Today"

    How can I adjust this to make it the last tab? Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    wkbNew.Worksheets(1).Copy After:=wkbOri.Sheets(wkbOri.Sheets.Count)
    wkbOri.Worksheets(wkbOri.Sheets.Count).Name = "Today"

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thankyou. This is an annoyance more than an issue. In the vbe, the sheet objects run: (if there are more than 10)
    1, 10, 11, 2 , ............ 9.
    So that I can keep them in a specific order in the tree, to avoid this I re-number them 01, 02, .............. 09, 10, 11 etc.

    When I use the code above to move a sheet into the workbook, it automatically takes the next available number. As I have renamed 1 to 01, the next available number is 1, so adding new sheets creates 1, 2, 3 etc instead of 12, 13, 14 etc. Is there a way to work around this annoyance?

    [attachment=82704:annoying.GIF]
    Attached Images Attached Images

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

    With ActiveWorkbook.VBProject.VBComponents
    .Item(.Count).Name = "Sheet" & Format(ActiveWorkbook.Worksheets.Count, "00")
    End With


    but this requires that the user trusts programmatic access to the Visual Basic project in the Trusted Sources tab of Tools | Macro | Security.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry Hans,

    I have removed:

    wkbOri.Worksheets(wkbOri.Sheets.Count).Name = "Bacs In Day 0 (1)"

    and added:

    With ActiveWorkbook.VBProject.VBComponents
    .Item(.Count).Name = "Bacs In Day 0 (1)" & Format(ActiveWorkbook.Worksheets.Count, "00")
    End With

    Am I mis-understanding?


    [attachment=82709:07.03.09.GIF]
    [attachment=82708:07.03.09_2.GIF]
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I thought you wanted to change the code names to Sheet01, Sheet02 etc.
    Spaces and punctuation are not allowed in the code names.

    Note: the code name is the name before the parentheses in the Project Explorer tree; this name must follow the rules for naming a variable in VBA. This is not the same as the name displayed in the sheet tab; that is the name between parentheses in the Project Explorer.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Aha, I understand:

    wkbOri.Worksheets(wkbOri.Sheets.Count).Name = "Bacs In Day 0 (1)"
    With ActiveWorkbook.VBProject.VBComponents
    .Item(.Count).Name = "Sheet" & Format(ActiveWorkbook.Worksheets.Count, "00")
    End With

    Tab shows: Bacs In Day 0 (1)

    Project explorer tree shows: Sheet12 (Bacs In Day 0 (1))

    Perfect.

    Thankyou!

    One final question:

    << this requires that the user trusts programmatic access to the Visual Basic project in the Trusted Sources tab of Tools | Macro | Security.

    I notice that the default setting here is "not trusted". Is there danger in trusting this? Is there anything that I need to consider?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='764004' date='07-Mar-2009 02:12']I notice that the default setting here is "not trusted". Is there danger in trusting this? Is there anything that I need to consider?[/quote]
    If your anti-virus software is up-to-date, it will prevent you from opening workbooks with macro viruses, so there's no risk.

  9. #9

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='VegasNath' post='763983' date='06-Mar-2009 19:25']How can I adjust this to make it the last tab? Thanks[/quote]
    See also Sorting worksheets in an Excel workbook

Posting Permissions

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