Select Column based on Sheet Name

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.

OfficeNovice
Thu May 08 11:11:02 CDT 2008

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
Thu May 08 12:09:03 CDT 2008

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.
> >
> >
> >

OfficeNovice
Thu May 08 12:12:01 CDT 2008

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
Thu May 08 12:24:00 CDT 2008

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.
> > > >
> > > >
> > > >

Jeff
Thu May 08 12:56:02 CDT 2008

"Bugaboo" <Bugaboo@discussions.microsoft.com> wrote in message
news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...

>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

OfficeNovice
Thu May 08 12:55:01 CDT 2008

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
Thu May 08 14:17:04 CDT 2008

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" <Bugaboo@discussions.microsoft.com> wrote in message
> news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...
>
> >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
Thu May 08 14:39:03 CDT 2008

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" <Bugaboo@discussions.microsoft.com> wrote in message
> > news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...
> >
> > >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

Bugaboo
Thu May 08 14:51:00 CDT 2008

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" <Bugaboo@discussions.microsoft.com> wrote in message
> > > news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...
> > >
> > > >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
Thu May 08 15:58:29 CDT 2008

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" <Bugaboo@discussions.microsoft.com> wrote in message
> > > > news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...
> > > >
> > > > >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

Bugaboo
Fri May 09 07:29:01 CDT 2008

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" <Bugaboo@discussions.microsoft.com> wrote in message
> > > > > news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...
> > > > >
> > > > > >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
Fri May 09 09:30:46 CDT 2008

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" <Bugaboo@discussions.microsoft.com> wrote in message
> > > > > > news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...
> > > > > >
> > > > > > >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

Bugaboo
Tue May 13 15:22:00 CDT 2008

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" <Bugaboo@discussions.microsoft.com> wrote in message
> > > > > > > news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...
> > > > > > >
> > > > > > > >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
Tue May 13 15:44:42 CDT 2008

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" <Bugaboo@discussions.microsoft.com> wrote in message
> > > > > > > > news:59044C1F-1C2A-42E8-A1B6-27109C53E197@microsoft.com...
> > > > > > > >
> > > > > > > > >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

Bugaboo
Wed May 14 07:30:00 CDT 2008

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.
> > > >
> > > > &quo