Format

F

Faboboren

I am running some codes for 15 sheets that I have recorded them and I want to
write the codes down in simpler instructions. Please any ideas to solve that?
Thanks

Macro 1: I am running this code for 14 different ranges in 15 sheets (first
instruction is to delete 16 sheet that no needed)


Sub Formatline1()

Sheets("WSP_TOC").Select
ActiveWindow.SelectedSheets.Delete


Sheets("WSP_Sheet1").Select
Range("A6:C6").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Macro 2

Sub Color()
'
' Color Macro
' Macro recorded 5/6/2008 by faborenh
'

'
Sheets("WSP_Sheet1").Select
Range("B6:C6").Select
Selection.Interior.ColorIndex = 8
Range("A7:A10").Select
Selection.Interior.ColorIndex = 44
Range("A11:A14").Select
Selection.Interior.ColorIndex = 35
Range("A15:A18").Select
Selection.Interior.ColorIndex = 33
Range("A19:A22").Select
Selection.Interior.ColorIndex = 36
Range("A23:A25").Select
Selection.Interior.ColorIndex = 4
Range("A26:A29").Select
Selection.Interior.ColorIndex = 39
Range("A30:A33").Select
Selection.Interior.ColorIndex = 3
Range("A34:A36").Select
Selection.Interior.ColorIndex = 42
Range("A37:A39").Select
Selection.Interior.ColorIndex = 46
Range("A40:A42").Select
Selection.Interior.ColorIndex = 43
Range("A43:A45").Select
Selection.Interior.ColorIndex = 38
Range("A46:A48").Select
Selection.Interior.ColorIndex = 8
Range("A49:A52").Select
Selection.Interior.ColorIndex = 6

Macro 3

Sub ConditionalFormat()
'
' ConditionalFormat Macro
' Macro recorded 5/6/2008 by faborenh
'

'
Sheets("WSP_Sheet1").Select
Range("C7:C52").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 3

Macro 4

Sub Letter12andBold()
'
' Letter12andBold Macro
' Macro recorded 5/6/2008 by faborenh
'

'
Sheets("WSP_Sheet1").Select
Range("A6:C52").Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Selection.Font.Bold = False
Selection.Font.Bold = True

Macro 5

Sub Adjust1column()
'
' HAdjust1column Macro
' Macro recorded 2006-11-23 by faborenh
'

'
Sheets("WSP_Sheet1").Select
Columns("A:A").EntireColumn.AutoFit

Macro 6
Sub Deselect()
'
' FDeselect Macro
' Macro recorded 2006-11-23 by faborenh
'

'
Range("C1").Select
Sheets("WSP_Sheet15").Select

Macro 7

Sub Pagesetup()
'
' GPagesetup Macro
' Macro recorded 2006-11-23 by faborenh
'

'
Sheets("WSP_Sheet1").Select
With ActiveSheet.Pagesetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.Pagesetup.PrintArea = "$A$1:$C$55"
With ActiveSheet.Pagesetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
 
P

Paul

Faboboren,

One thing to learn when using Excel programmatically is that you do not
necessarily have to select a range/worksheet/open workbook to do something
with them.

For instance with your second macro it could be written like this.

Sub Color()

Sheets("WSP_Sheet1").Select
Range("B6:C6").Interior.ColorIndex = 8
Range("A7:A10").Interior.ColorIndex = 44
Range("A11:A14").Interior.ColorIndex = 35
Range("A15:A18").Interior.ColorIndex = 33
Range("A19:A22").Interior.ColorIndex = 36
Range("A23:A25").Interior.ColorIndex = 4
Range("A26:A29").Interior.ColorIndex = 39
Range("A30:A33").Interior.ColorIndex = 3
Range("A34:A36").Interior.ColorIndex = 42
Range("A37:A39").Interior.ColorIndex = 46
Range("A40:A42").Interior.ColorIndex = 43
Range("A43:A45").Interior.ColorIndex = 38
Range("A46:A48").Interior.ColorIndex = 8
Range("A49:A52").Interior.ColorIndex = 6

End Sub

Regards

Paul
 
D

Dave Peterson

And you don't have to select that worksheet, either:

Sub Color()

with workSheets("WSP_Sheet1").Select
.Range("B6:C6").Interior.ColorIndex = 8
.Range("A7:A10").Interior.ColorIndex = 44
.Range("A11:A14").Interior.ColorIndex = 35
.Range("A15:A18").Interior.ColorIndex = 33
.Range("A19:A22").Interior.ColorIndex = 36
.Range("A23:A25").Interior.ColorIndex = 4
.Range("A26:A29").Interior.ColorIndex = 39
.Range("A30:A33").Interior.ColorIndex = 3
.Range("A34:A36").Interior.ColorIndex = 42
.Range("A37:A39").Interior.ColorIndex = 46
.Range("A40:A42").Interior.ColorIndex = 43
.Range("A43:A45").Interior.ColorIndex = 38
.Range("A46:A48").Interior.ColorIndex = 8
.Range("A49:A52").Interior.ColorIndex = 6
end with

End Sub

The leading dots in front of .range(...) means that this belongs to the object
in the previous With statement. In this case the worksheet named WSP_Sheet1.
 
J

JLGWhiz

I interpreted your post that you want to apply each
macro to each sheet in the workbook except the one
which you delete in Macro 1. What I have done is
modify your macros to remove the selects and acivates
since they are not necessary for the code to work.
I did not test each macro, so you could get some error
messages. If you do, line that is highlighted and the
message and post back here to this thread for more
help. I added a control macro, which is the one you
will need to stard with and it will run all of the
others. Macro 5 was incoprated into Macro 1 and I did
nothing with Macro 6 because I don't believe it is
needed. Since the same names are used for the macros
you will need to either comment out the olld ones, or delete
them before pasting these into the code module.

Sub MacroControl()
Format1
Color
ConditionalFormat
Letter12andBold
Pagesetup
End Sub

Sub Formatline1()
Sheets("WSP_TOC").Delete
For Each Sh In ThisWorkbook.Sheets
Columns("A:A").EntireColumn.AutoFit
With Sh.Range("A6:C6").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Sh.Range("A6:C6").BorderAround _
LineStyle:=xlContinuous, Weight:=xlThin
Next
End Sub

Sub Color()
' Color Macro
' Macro recorded 5/6/2008 by faborenh
'
For Each Sh In ThisWorkbook.Sheets
With Sh
.Range("B6:C6").Interior.ColorIndex = 8
.Range("A7:A10").Interior.ColorIndex = 44
.Range("A11:A14").Interior.ColorIndex = 35
.Range("A15:A18").Interior.ColorIndex = 33
.Range("A19:A22").Interior.ColorIndex = 36
.Range("A23:A25").Interior.ColorIndex = 4
.Range("A26:A29").Interior.ColorIndex = 39
.Range("A30:A33").Interior.ColorIndex = 3
.Range("A34:A36").Interior.ColorIndex = 42
.Range("A37:A39").Interior.ColorIndex = 46
.Range("A40:A42").Interior.ColorIndex = 43
.Range("A43:A45").Interior.ColorIndex = 38
.Range("A46:A48").Interior.ColorIndex = 8
.Range("A49:A52").Interior.ColorIndex = 6
End With
Next
End Sub

Sub ConditionalFormat()
'
' ConditionalFormat Macro
' Macro recorded 5/6/2008 by faborenh
'
For Each Sh In ThisWorkbook.Sheets
Sh.Range("C7:C52").FormatConditions.Delete
Sh.Range("C7:C52").FormatConditions.Add Type:=xlCellValue,
Operator:=xlLess, _
Formula1:="0"
Sh.Range("C7:C52").FormatConditions(1).Font.ColorIndex = 3
Next
End Sub

Sub Letter12andBold()
'
' Letter12andBold Macro
' Macro recorded 5/6/2008 by faborenh
'
For Each Sh In ThisWorkbook.Sheets
With Sh.Range("A6:C52").Font
.Name = "Arial"
.Size = 12
.ColorIndex = 1
.Bold = True
End With
Next
End Sub


Sub Pagesetup()
'
' GPagesetup Macro
' Macro recorded 2006-11-23 by faborenh
''
For Each Sh In ThisWorkbook.Sheets
With Sh.Pagesetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = "$A$1:$C$55"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
Next
End Sub
 
R

Rick Rothstein

Dave meant to write the fist line this way (without the Select keyword)...

With WorkSheets("WSP_Sheet1")
 
J

JLGWhiz

Just noticed that I left part of a macro name out. Substute this for the
ControlMacro

Sub MacroControl()
Formatline1
Color
ConditionalFormat
Letter12andBold
Pagesetup
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help needed with borders 2
Change code help 0
Hlp! Adv Filter - Non-Contiguous rows don't copy 11
Macro to sort, format, sum, a dynamic set of data 2
Formatting stop when reach blank cell 6
Border formatting row of cells 22
Slow Print out of the last of 3 pages in this macro any ideas????? Need Help 0
Run Time Error 1004 2

Top