Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to extract Sheet Names I need VBA code that (2007)

    Macro to extract Sheet Names

    I tried to write code that will extract the sheet names where the sheet names are coloured in Black, to a sheet named 'to be corrected", but the sheet named that are coloured blacl are not being extracteed to the sheet named "to be corrected"

    Sub Extract()

    Dim sheet As Worksheet

    For Each sheet In ThisWorkbook.Worksheets

    If sheet.Tab.ColorIndex = 1 Then Sheets("To be corrected").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = sheet.Name

    Next sheet

    End Sub

    Your assistance will be most appreciated

    Howard
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to extract Sheet Names I need VBA code that (2007)

    Your code works for me. The example sheet does not contain the code you posted - perhaps the sub was put into another workbook? If not, are you getting any error messages?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro to extract Sheet Names I need VBA code that (2007)

    Hi John

    The macro runs without any errors, but the sheets ,that are coloured in black i.e "client queries" and "legal fee" are not extracted to the sheet "To be corrected"

    I want these names to appear on the sheet "To be corrected"

    Your assistance will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to extract Sheet Names I need VBA code that (2007)

    I downloaded your workbook, ignored external links, enabled macros, and it ran for me immediately. We are missing something simple.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro to extract Sheet Names I need VBA code that (2007)

    Hi John

    Thanks for the reply. I am using Excel version 2007. Would this make any difference to the macro ColorIndex = 1 (Black)

    When I have made the sheet color black, I have right clicked on the sheet, selected tab color "black, text1" under theme colors

    Does this help you any any way to resolve my problem, as when I activate the macro, the macro runs error free, but the sheet names that have been colored in black are not extracted to sheet "To be corrected"?

    Regards

    Howard

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to extract Sheet Names I need VBA code that (2007)

    I'm using Excel 2003, I don't have a copy of Excel 2007. Can any other Loungers assist?
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro to extract Sheet Names I need VBA code that (2007)

    Hi John

    as you mentioned earlier, the solution would be simple. I amended my code as follows and it works. I was using code for Excel 2003

    Sub Extract()

    Dim sheet As Worksheet

    For Each sheet In ThisWorkbook.Worksheets

    If sheet.Tab.ThemeColor = xlThemeColorLight1 Then Sheets("To be corrected").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = sheet.Name

    Next sheet

    End Sub

    Thanks for all the effort

    Regards

    Howard

Posting Permissions

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