Select Column based on Sheet Name

[Bugaboo]

I have a macro that will copy and add a sheet, and name it for each name in
the first row.

I now need to select the column in each sheet where the sheet name and
header name are the same and set the background to gray. The number of
columns and rows will vary.

Is this possible to do?

Thanks for all your help. I use this site a lot.

 

[Office_Novice]

Something like this i assume?

If ActiveSheet.Name = "Sheet1" Then
Columns("B").Select
End If

 

[Bugaboo]

Something like that. What I am looking for is if the active sheet name is
"black" then select the column where the header name is black. I need it to
loop through all the sheets until are done.

 

[Office_Novice]

By Header you mean the first row in each column?

 

[Bugaboo]

Yes.

By Header you mean the first row in each column?

 

[Office_Novice]

Modify this to suit your needs. I think it will do what you want.

Sub YourProcName()
Dim wks As Worksheet
Dim i As Integer
For i = 1 To 3 ' <--How ever many sheet you have
With Worksheets(i)
.Activate
Set wks = ActiveSheet
Range("A1").Select
Do Until ActiveCell.Address = "$IV$1"
If wks.Name = ActiveCell.Value Then
ActiveCell.EntireColumn.Interior.ColorIndex = 6 '<--
Whatever you want to have happen
If wks.Name <> ActiveCell.Value Then
ActiveCell.Offset(0, 1).Select
End If
End If
ActiveCell.Offset(0, 1).Select
Loop
End With
Next i
End Sub
'This code will loop through the top row of every sheet in a workbook and
hightlight the
'column if first cell in the column matchs the sheet name.

 

[Bugaboo]

Jeff,

That would be great if I can get it to do it while creating the sheets. I'm
just not sure where it would go.

Here is the code I am using to create my sheets:

Sub DCreateNameSheets()

Dim wstemp As Worksheet
Dim Rng As Range
Dim ListRng As Range
Set wstemp = Worksheets("Transposed")
Set ListRng = Range(Range("B1"), Range("B1").End(xlToRight))
For Each Rng In ListRng
If Rng.Text <> "" Then
wstemp.Copy After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = Rng.Text
End If
Next Rng
End Sub

 

[Dave Peterson]

Each time you create a new worksheet, then that worksheet should have the column
that the was used for the name of the new worksheet.

If the name is in X1, then column X should be selected, right?

Option Explicit
Sub DCreateNameSheets()

Dim wsTemp As Worksheet
Dim Rng As Range
Dim ListRng As Range
Dim iCol As Long

Set wsTemp = Worksheets("Transposed")

With wsTemp
Set ListRng = .Range("B1", .Range("B1").End(xlToRight))
End With

For Each Rng In ListRng.Cells
If Rng.Text <> "" Then
wsTemp.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Rng.Text
ActiveSheet.Columns(Rng.Column).Select
End If
Next Rng
End Sub

 

[Bugaboo]

Dave,

That is correct.

Each time you create a new worksheet, then that worksheet should have the column
that the was used for the name of the new worksheet.

If the name is in X1, then column X should be selected, right?

Option Explicit
Sub DCreateNameSheets()

Dim wsTemp As Worksheet
Dim Rng As Range
Dim ListRng As Range
Dim iCol As Long

Set wsTemp = Worksheets("Transposed")

With wsTemp
Set ListRng = .Range("B1", .Range("B1").End(xlToRight))
End With

For Each Rng In ListRng.Cells
If Rng.Text <> "" Then
wsTemp.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Rng.Text
ActiveSheet.Columns(Rng.Column).Select
End If
Next Rng
End Sub

 

[Dave Peterson]

The assumption I made is correct or the macro worked ok?

Or maybe both????

 

[Bugaboo]

Your assumption was correct.

The assumption I made is correct or the macro worked ok?

Or maybe both????

 

[Dave Peterson]

What happened when you tried the code?

Your assumption was correct.

 

[Bugaboo]

Jeff's code works if I run it on each sheet. I am not sure where I would put
the code to have it select and bold when creating the sheets.

 

[Dave Peterson]

Did you try the code I suggested?

Jeff's code works if I run it on each sheet. I am not sure where I would put
the code to have it select and bold when creating the sheets.

 

[Bugaboo]

Your code selects the right column. Now how do I get it to bold it?

 

[Dave Peterson]

Add a line after:
ActiveSheet.Columns(Rng.Column).Select
ActiveSheet.Columns(Rng.Column).Font.bold = true

If you want to do more, try recording a macro when you do it manually. It may
make it easy to wedge it into your code.

Your code selects the right column. Now how do I get it to bold it?