Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Copying workbook tab colour

    Afternoon All,

    Must be a really simple thing but i cant get it right.

    I have workbook 1 called Pub_workbook (its a public variable)
    In that i have a number of sheets (spreadsheet which is passed in as a parameter).

    My VB code copies each sheet one at a time to a new workbook "Book1".

    I want to copy over the colour of the tab also from Pub_workbook to Book1.

    At the moment im doing:
    Code:
     ActiveWorkbook.Sheets(spreadsheet).Tab.ColorIndex = Colour
    where i pass in the number for code.

    I want to change this so that the new sheet will get the colour from the original sheet.

    Ive tried:

    Code:
     ActiveWorkbook.Sheets(spreadsheet).Tab.ColorIndex =  Pub_workbook.Sheets(spreadsheet).Tab.ColorIndex
    but get error:

    Run Time error '424'
    Object Required.

    can anyone give me pointers as to where / what i have wrong?

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Could be that the sheet name(s) doesn't exist or the Pub_ workbook is not open.
    Also, I recommend using "Color" not "ColorIndex" to define colors in xl2007 and later: .Tab.Color

    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (free and commercial excel programs)

  3. #3
    New Lounger
    Join Date
    Apr 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The sheet exists, as other functions around it work,

    I have copied in the full script below:

    Code:
    Public Pub_worksheet
    
    Sub RSV()
    '
        Pub_worksheet = "Performance report.xls"
        Workbooks.Add
        Windows(Pub_worksheet).Activate
        Application.WindowState = xlMaximized
        Windows.Arrange ArrangeStyle:=xlHorizontal
        
    '   Set up new workbook with sheets
    
        Windows("Book1").Activate
        ActiveWindow.TabRatio = 0.957
        
        Windows(Pub_worksheet).Activate
    
        Call Copy("Performance", 1, 0)
        Call Copy("Targets", 0, 0)
        Call Copy("PATF Daily Mov't", 0, 0)
        Call Copy("PATF Daily Mov't - Feb 11 - Jun", 41, 0)
        Call Copy("PATF Inv", 0, 1)
        Call Copy("PATF Reds", 0, 1)
        Call Copy("PATF2 Daily Mov't", 0, 0)
        Call Copy("PATF2 Daily Mov't - Feb 11- Jun", 41, 0)
        Call Copy("PATF2 Inv", 0, 1)
        Call Copy("PATF2 Reds", 0, 1)
        Call Copy("FX", 0, 0)
        Call Copy("PATF Inv09-10 (Unknowns)", 0, 0)
        Call Copy("PATF2 Inv09-10 (Unknowns)", 0, 0)
     
        
    '   Delete extra sheets
        Windows("Book1").Activate
        Application.DisplayAlerts = False
        Sheets("Sheet1").Select
        ActiveWindow.SelectedSheets.Delete
    
        Application.DisplayAlerts = False
        Sheets("Sheet2").Select
        ActiveWindow.SelectedSheets.Delete
    
        Application.DisplayAlerts = False
        Sheets("Sheet3").Select
        ActiveWindow.SelectedSheets.Delete
        
          
    '   Select Performance Report Sheet to finish
        Sheets("Performance").Select
        Windows("Performance report.xls").Activate
        Sheets("Performance").Select
        Range("A1").Select
    
    End Sub
    
    Sub Copy(spreadsheet As String, Colour As Integer, Set_Range As Integer)
        Windows("Book1").Activate
        Sheets.Add.Name = spreadsheet
        ActiveSheet.Move after:=Worksheets(Worksheets.Count)
        ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
        Sheets(spreadsheet).Select
        
        Windows(Pub_worksheet).Activate
        Sheets(spreadsheet).Select
        Cells.Select
        Selection.Copy
        
        Windows("Book1").Activate
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            
        If Colour > 0 Then
        ActiveWorkbook.Sheets(spreadsheet).Tab.ColorIndex = Colour
      '  ActiveWorkbook.Sheets(spreadsheet).Tab.ColorIndex = Pub_workbook.Sheets(spreadsheet).Tab.ColorIndex
        End If
        
        If Set_Range > 0 Then
        Range("A3:R3").Select
        Selection.AutoFilter
        Range("A5").Select
        ActiveWindow.FreezePanes = True
        End If
        
        Range("A1").Select
        Windows(Pub_worksheet).Activate
        Range("A1").Select
    End Sub
    Its a bit rough round the edges as yet, im working on tidying it up...

    Also any ideas on how to create a new enpty workbook rather then having Sheet 1, Sheet 2 and Sheet 3 created for me to delete them later?
    Last edited by unclebob; 2011-04-15 at 04:45.

  4. #4
    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
    You haven't declared Pub_workbook anywhere that I can see - it's Pub_worksheet and it's a string not a Workbook object, so:
    Code:
     ActiveWorkbook.Sheets(spreadsheet).Tab.ColorIndex =  Workbooks(Pub_worksheet).Sheets(spreadsheet).Tab.ColorIndex
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    unclebob (2011-04-15)

  6. #5
    New Lounger
    Join Date
    Apr 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for that!

    Works Great!

Posting Permissions

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