Copying workbook tab colour

Topic

New Reply

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?

Reply | Quote

Replies

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)

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

Thanks for that!

Works Great!

Reply | Quote