Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Header on Multiple Sheets without Changing Other Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting using multiple sheets | Excel Worksheet Functions | |||
Why Not Conditional Formatting For Multiple Sheets? | New Users to Excel | |||
How do I copy print formatting to multiple sheets in a workbook? | Excel Discussion (Misc queries) | |||
Looping through Sheets | Excel Programming |