looping formatting multiple sheets
 

I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's) copy
to different sheets macro I end up with 34 sheets (including the original,
but I'd like all sheet in the workbook formatted no matter the number, which
could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C then
insert column

Thanks for any and all help

Ian

looping formatting multiple sheets
 

This code should do what you asked...

With Worksheets("Sheet1")
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
End With
End With

Note that I am executing your Column Insert request before the formatting is
applied to A1:S1... if you do it afterwards, the insert operation will
extend the formats to Column T.

Rick



"ian bartlett" wrote in message
news:3n6ak.40168$kx.25915@pd7urf3no...
I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's)
copy to different sheets macro I end up with 34 sheets (including the
original, but I'd like all sheet in the workbook formatted no matter the
number, which could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C then
insert column

Thanks for any and all help

Ian

looping formatting multiple sheets
 

Rick

I'm not that well versed but I don't think it will do all the sheets ?

Thanks for you input
Ian

"ian bartlett" wrote in message
news:3n6ak.40168$kx.25915@pd7urf3no...
I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's)
copy to different sheets macro I end up with 34 sheets (including the
original, but I'd like all sheet in the workbook formatted no matter the
number, which could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C then
insert column

Thanks for any and all help

Ian

looping formatting multiple sheets
 

Give this a try...

Dim SH As Worksheet
For Each SH In Worksheets
With Worksheets(SH)
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
End With
End With
Next

Rick


"ian bartlett" wrote in message
news:Hg8ak.65802$gc5.1815@pd7urf2no...
Rick

I'm not that well versed but I don't think it will do all the sheets ?

Thanks for you input
Ian

"ian bartlett" wrote in message
news:3n6ak.40168$kx.25915@pd7urf3no...
I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's)
copy to different sheets macro I end up with 34 sheets (including the
original, but I'd like all sheet in the workbook formatted no matter the
number, which could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C then
insert column

Thanks for any and all help

Ian

looping formatting multiple sheets
 

Rick

Thanks very much for you help

Ian

"ian bartlett" wrote in message
news:3n6ak.40168$kx.25915@pd7urf3no...
I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's)
copy to different sheets macro I end up with 34 sheets (including the
original, but I'd like all sheet in the workbook formatted no matter the
number, which could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C then
insert column

Thanks for any and all help

Ian

looping formatting multiple sheets
 

You are welcome. For future questions you might post, you should post back
to the message you are responding to rather than posting back to your
original message. It makes the thread easier to follow (especially for
someone who may read it at some future date in the Google archives).

Rick


"ian bartlett" wrote in message
news:m39ak.40680$kx.25019@pd7urf3no...
Rick

Thanks very much for you help

Ian

"ian bartlett" wrote in message
news:3n6ak.40168$kx.25915@pd7urf3no...
I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's)
copy to different sheets macro I end up with 34 sheets (including the
original, but I'd like all sheet in the workbook formatted no matter the
number, which could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C then
insert column

Thanks for any and all help

Ian

looping formatting multiple sheets
 

I spoke to soon Rick I'm getting an error noted in body

Public Sub Fix_Up_Sheets()
Dim SH As Worksheet
For Each SH In Worksheets
With Worksheets(SH) 'error at this line Type mismatch Placed in regular
module
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
End With
End With
Next

End Sub

"Rick Rothstein (MVP - VB)" wrote in
message ...
You are welcome. For future questions you might post, you should post back
to the message you are responding to rather than posting back to your
original message. It makes the thread easier to follow (especially for
someone who may read it at some future date in the Google archives).

Rick


"ian bartlett" wrote in message
news:m39ak.40680$kx.25019@pd7urf3no...
Rick

Thanks very much for you help

Ian

"ian bartlett" wrote in message
news:3n6ak.40168$kx.25915@pd7urf3no...
I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's)
copy to different sheets macro I end up with 34 sheets (including the
original, but I'd like all sheet in the workbook formatted no matter the
number, which could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C then
insert column

Thanks for any and all help

Ian

looping formatting multiple sheets
 

Sorry... I forgot that I declared SH as a Worksheet... the Worksheets()
property "housing" is not required in the With statement. This code should
work....

Public Sub Fix_Up_Sheets()
Dim SH As Worksheet
For Each SH In Worksheets
With SH
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
End With
End With
Next
End Sub

Rick


"ian bartlett" wrote in message
news:eA9ak.40704$kx.3722@pd7urf3no...
I spoke to soon Rick I'm getting an error noted in body

Public Sub Fix_Up_Sheets()
Dim SH As Worksheet
For Each SH In Worksheets
With Worksheets(SH) 'error at this line Type mismatch Placed in regular
module
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
End With
End With
Next

End Sub

"Rick Rothstein (MVP - VB)" wrote in
message ...
You are welcome. For future questions you might post, you should post
back to the message you are responding to rather than posting back to
your original message. It makes the thread easier to follow (especially
for someone who may read it at some future date in the Google archives).

Rick


"ian bartlett" wrote in message
news:m39ak.40680$kx.25019@pd7urf3no...
Rick

Thanks very much for you help

Ian

"ian bartlett" wrote in message
news:3n6ak.40168$kx.25915@pd7urf3no...
I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's)
copy to different sheets macro I end up with 34 sheets (including the
original, but I'd like all sheet in the workbook formatted no matter
the number, which could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C
then insert column

Thanks for any and all help

Ian

looping formatting multiple sheets
 

Rick

I can't pretend to understand this but it is working as advertised!!

Thanks again

"Rick Rothstein (MVP - VB)" wrote in
message ...
Sorry... I forgot that I declared SH as a Worksheet... the Worksheets()
property "housing" is not required in the With statement. This code should
work....

Public Sub Fix_Up_Sheets()
Dim SH As Worksheet
For Each SH In Worksheets
With SH
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
End With
End With
Next
End Sub

Rick


"ian bartlett" wrote in message
news:eA9ak.40704$kx.3722@pd7urf3no...
I spoke to soon Rick I'm getting an error noted in body

Public Sub Fix_Up_Sheets()
Dim SH As Worksheet
For Each SH In Worksheets
With Worksheets(SH) 'error at this line Type mismatch Placed in regular
module
.Rows().RowHeight = 78
.Rows(1).RowHeight = 15
.Columns("C").Insert
With .Range("A1:S1")
.Font.Bold = True
.Font.Color = vbRed
.Cells.Interior.Color = vbYellow
End With
End With
Next

End Sub

"Rick Rothstein (MVP - VB)" wrote
in message ...
You are welcome. For future questions you might post, you should post
back to the message you are responding to rather than posting back to
your original message. It makes the thread easier to follow (especially
for someone who may read it at some future date in the Google archives).

Rick


"ian bartlett" wrote in message
news:m39ak.40680$kx.25019@pd7urf3no...
Rick

Thanks very much for you help

Ian

"ian bartlett" wrote in message
news:3n6ak.40168$kx.25915@pd7urf3no...
I'm sure I can accomplish this with the macro recorder, but it would
generate a ton of unneccesary code.

I import a text file into Excel 2003 then using Debra's (Contexture's)
copy to different sheets macro I end up with 34 sheets (including the
original, but I'd like all sheet in the workbook formatted no matter
the number, which could possibly change)

I'd like to

1. Header row A1:S1 row height 15 font red bold, background yellow
2. Remainder or rows height 78
3. Then insert a column not sure of syntax, manually click column C
then insert column

Thanks for any and all help

Ian