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.

Something like this i assume?

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

"Bugaboo" wrote:

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.

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" wrote:

Something like this i assume?

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

"Bugaboo" wrote:

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.

By Header you mean the first row in each column?

"Bugaboo" wrote:

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" wrote:

Something like this i assume?

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

"Bugaboo" wrote:

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.

Yes.

"Office_Novice" wrote:

By Header you mean the first row in each column?

"Bugaboo" wrote:

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" wrote:

Something like this i assume?

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

"Bugaboo" wrote:

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.

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" wrote:

Yes.

"Office_Novice" wrote:

By Header you mean the first row in each column?

"Bugaboo" wrote:

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" wrote:

Something like this i assume?

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

"Bugaboo" wrote:

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.

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next

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






"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson

Dave,

That is correct.



"Dave Peterson" wrote:

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson

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

Or maybe both????

Bugaboo wrote:

Dave,

That is correct.

"Dave Peterson" wrote:

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson


--

Dave Peterson

Your assumption was correct.

"Dave Peterson" wrote:

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

Or maybe both????

Bugaboo wrote:

Dave,

That is correct.

"Dave Peterson" wrote:

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson


--

Dave Peterson

What happened when you tried the code?

Bugaboo wrote:

Your assumption was correct.

"Dave Peterson" wrote:

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

Or maybe both????

Bugaboo wrote:

Dave,

That is correct.

"Dave Peterson" wrote:

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

What happened when you tried the code?

Bugaboo wrote:

Your assumption was correct.

"Dave Peterson" wrote:

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

Or maybe both????

Bugaboo wrote:

Dave,

That is correct.

"Dave Peterson" wrote:

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Did you try the code I suggested?

Bugaboo wrote:

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" wrote:

What happened when you tried the code?

Bugaboo wrote:

Your assumption was correct.

"Dave Peterson" wrote:

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

Or maybe both????

Bugaboo wrote:

Dave,

That is correct.

"Dave Peterson" wrote:

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

Did you try the code I suggested?

Bugaboo wrote:

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" wrote:

What happened when you tried the code?

Bugaboo wrote:

Your assumption was correct.

"Dave Peterson" wrote:

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

Or maybe both????

Bugaboo wrote:

Dave,

That is correct.

"Dave Peterson" wrote:

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

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.

Bugaboo wrote:

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

"Dave Peterson" wrote:

Did you try the code I suggested?

Bugaboo wrote:

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" wrote:

What happened when you tried the code?

Bugaboo wrote:

Your assumption was correct.

"Dave Peterson" wrote:

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

Or maybe both????

Bugaboo wrote:

Dave,

That is correct.

"Dave Peterson" wrote:

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 wrote:

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

"Jeff Johnson" wrote:

"Bugaboo" wrote in message
...

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?

Yes, and I'll show you how, but why not do it WHILE you're creating your
copies? Yoiu'll already know which column you're dealing with, and it'll
make the following code mostly unnecessary. By the way, "set the background
to gray" is vague, so I'm setting the fill color of entire column to gray.
If you want something more specific, give more specific requirements.

Dim sheetName As String
Dim x As Long

sheetName = ActiveSheet.Name

For x = 1 To 256 ' (Or however many header columns you have)
If ActiveSheet.Cells(1, x).Text = sheetName Then
ActiveSheet.Cells(1, x).EntireColumn.Interior.Color = RGB(192, 192,
192)
Exit Sub
End If
Next




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson