On Jan 29, 2:54*pm, Jim G wrote:
I've recorded the code below to create a new sheet if one does not exist and
then populate control fields before copying them to a template. *I get the
error "Object variable or With Block variable not set" *at "sh.Activate"
after the sheet is created. *Have I put this in the wrong spot?

Is there a simplier way to write the code for the column headers?

Sub SetControlSheet()
'
' SetControl Macro
' Macro recorded 29/01/2008 by Jim Gray
'
'create Contol sheet if it does not exist
Dim sh As Worksheet

On Error Resume Next
* * Set sh = Worksheets("Control")
* * On Error GoTo 0
* * If sh Is Nothing Then
* * * * Worksheets.Add.Name = ("Control")
* * * * sh.Activate 'errorObject variable or With Block variable not set
* * With ActiveSheet
* * Range("A2").Select
* * ActiveCell.FormulaR1C1 = "Start of FY"
* * Range("B2").Select
* * ActiveCell.FormulaR1C1 = "7/31/2007"
* * Range("A4").Select
* * ActiveCell.FormulaR1C1 = "Date Header Row"

* * 'set date formulas
* * Range("E5").Select
* * ActiveCell.FormulaR1C1 = "=Control!R2C2"
* * Range("H5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("K5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("N5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("Q5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("T5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("W5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("Z5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AC5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AF5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AI5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"
* * Range("AL5").Select
* * ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

* * 'set col header
* * Range("G5") = "Costs After": Range("J5") = "Costs After": Range("M5") =
"Costs After": Range("P5") = "Costs After": Range("S5") = "Costs After"
* * Range("v5") = "Costs After": Range("y5") = "Costs After": Range("AB5") =
"Costs After": Range("AE5") = "Costs After": Range("AH5") = "Costs After":
Range("AK5") = "Costs After"
* * End With
* * Else

* * 'copy new header row to Cheops Sheet
* * Sheets("Control").Select
* * Rows("5:5").Select
* * Selection.Copy
* * Sheets("Cheops").Select
* * Rows("13:13").Select
* * ActiveSheet.Paste

End If
End Sub

--
Jim

Try to enter following line before sh.activate:
set sh = activesheet

otherwise sh would be nothing and you cannot activate nothing.

For your other problem you could do following:

'set date formulas
Range("E5").FormulaR1C1 = "=Control!R2C2"
Range("H5,K5,N5,Q5,T5,W5,Z5,AC5,AF5,AI5,AL5").Form ulaR1C1 =
"=DATE(YEAR(RC[-3]),MONTH(RC[-3])+1+1,0)"

there will be line wraps...look out for them!

Hth Carlo