On Jan 30, 9:24*am, Jim G wrote:
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- Hide quoted text -

- Show quoted text -

You're welcome, thanks for the feedback

Carlo