Make code more concise

[BEEJAY]

Having recently read numerous posts about staying away from Select,
Selection, etc. I was able to greatly reduce a code set that I'm currently
working on.
However, I did run stuck on some, as well.
I tried to condense the following 6 lines to 3, without success.

Sheets("Contract").Select
Sheets("Contract").Copy Before:=Sheets(1)
Sheets("Contract (2)").Select
Sheets("Contract (2)").Name = "COM.CALC."
Sheets("COM.CALC.").Select
ActiveWorkbook.Sheets("COM.CALC.").Tab.ColorIndex = 9

' Sheets("Contract").CopyBefore:= Sheets (1)
' Sheets("Contract(2)").Name = "COM.CALC."
' Sheets("COM.CALC.").Tab.ColorIndex = 9

What am I not understanding here?

Also, various efferts on the following have been unsuccessful, as well.
I did try to use info I found in this ng, but perhaps the number of lines
are a problem.
How can something like the following be condensed?

Range("B26:E26").Select
Selection.Font.Size = 16
Selection.Font.Bold = True
Rows("26:26").RowHeight = 21#
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
End With
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With

With some help on the above and hopefully some explanation, I can redo a
substantial number (in this module alone).

Thank-you

 

[Joel]

This line you missed a space between copy and befor
Sheets("Contract").Copy Before:= Sheets (1)

remove the period before the last double quote. Make sure you get all the
spacing right in the sheet name "Contract(2)". I usually copy the name from
the sheet tab and paste it into the code adding double quotes at the
beginning and end.
Sheets("Contract(2)").Name = "COM.CALC."

This line will fail if the line above doesn't work
Sheets("COM.CALC.").Tab.ColorIndex = 9

 

[Jim Thomlinson]

Good on you for getting away from selecting... Now it is just a matter of
creating proper references to the sheets and ranges you want. Recorded macros
create the references by selecting things. When you don't select things you
still need to create those references. The rule hese is to be explicit.
Refernce the sheet and the ranges. Do not rely on the active sheets as that
is a bad practice... One thing that can help os to create sheet and range
objects. Set these objects and then referencing becomes quite a bit easier.

dim wks as worksheet

set wks = Sheets("Contract")
'note when you type wks you get an intellisence drop down
wks.copy Before:=Sheets(1)
set wks = Sheets("Contract (2)")
wks.name = "COM.CALC."
wks.Tab.ColorIndex = 9


dim rng as range
set rng = sheets("Mysheet").Range("B26:E26")
with rng
..Font.Size = 16
..Font.Bold = True
..entirerow.RowHeight = 21
..HorizontalAlignment = xlCenterAcrossSelection
..Interior.ColorIndex = 40
End With

 

[BEEJAY]

As always, your comments are appreciated so much - together with the proper
code to follow along with for an actual application.
I constantly try to learn from the answers I get from this ng, and then look
up (again) in my various books.

Thank-you