VBA Range Property

[AndrewCrisp]

I am new to vba in Excel and have written a macro that works well but
keeps falling over.

The code is as follows:

Dim costcentre As Range
Dim LastSht As Long
Sheets("Header").Select
LastSht = Sheets.Count
Set costcentre = Range("e4")
Sheets("Template").Select
Sheets("Template").Copy after:=Sheets(LastSht)
Sheets("Template (2)").Select
Range("av7").Value = costcentre
Range("Q17:ab17").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("q21:ab43").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("Template (2)").Name = costcentre
Columns("A:O").Hidden = True
Columns("Ai:dd").Hidden = True
'Sheets("Header").Select
'Range("costcentre").Select

It is this last line that falls over. It is ok if I use a specifc
reference eg "e4" but I don't want to do this as I want to offset one
cell down each time through when I add a loop. Can I not use the
declared name to go to a cell?

Any halp much appreciated.

Thanks

Andrew

 

[Ken Johnson]

Hi Andrew,

Excel is looking for a named range named "costcentre" and probably
can't find such a named range.

Your code has set the variable "costcentre" to represent the range E4,
so I think your last line should be...

costcentre.select

Ken Johnson

 

[AndrewCrisp]

Ken

Simple but did the trick.

Many thanks

Andrew

 

[Ken Johnson]

You're welcome Andrew.
Thanks for the feedback.

Ken Johnson