CODEWITHSUNDEEP
excelvbacolorscopy
AngelOfDef
AngelOfDef

Reputation: 15

Move to new workbook as values by tab color?

I have a large report with multiple data tables and about 12-20 sheets of broken out reports. What I currently have to do is Move/Copy, all the tabs, and then go through and copy and paste values to drop the pivot references. I've color coded which ones should be grouped together.

I am a VBA novice for sure, but wanted to know if someone could help me out. Is there anyway to reference tab color in VBA? If so could someone provide a quick code that might do this for me.

Upvotes: 1

Views: 96

Answers (2)

klib
klib

Reputation: 697

I am not sure exactly what you are trying to do but in addition to what Tim said you can access the color of a tab by using the color index, for example:

ActiveSheet.Tab.ColorIndex

This returns a number that matches simple colors.

There is a table that you might find usful for this here: http://dmcritchie.mvps.org/excel/colors.htm

To loop through your sheets you could write a simple loop such as:

Dim Sheetcount As Integer
Dim i As Integer
Sheetcount = ActiveWorkbook.Worksheets.Count

For i = 1 To Sheetcount
  If ActiveWorkbook.Sheets(i).Tab.ColorIndex = 3 Then 'just an example color
         'your code here
Next i

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166540

If a tab has had a color assigned then

Activesheet.Tab.Color

will return the RGB value (as a Long). If no color has been assigned it returns False

Upvotes: 2

Related Questions