Thanks Carlo, both worked perfectly and another lesson learned!
--
Jim


"carlo" wrote:

On Jan 29, 3:59 pm, carlo wrote:
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- Hide quoted text -

- Show quoted text -

Oh misunderstanding...but the other works as well, you can use the
same for the headers:
'set col header
Range("G5,J5,M5,P5,S5,V5,Y5,AB5,AE5,AH5,AK5") = "Costs After"

cheers
Carlo