Using "Names" in VBA codes

E

Ed

Hello I would like to know in which way I can have values as "Names" or
something like that to use them in a code... to replace for example in the
code below:

Worksheets("Budget").Range("G24")

With something like a Name, so if I happen to add or delete rows above row G
the reference is still valid, and it doesn't just take G24 regardless of what
it contains.

Sub Data()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("B1").Value
Range("b19:f" & LastRow).FillDown

Range("c" & LastRow + 2) = Worksheets("Budget").Range("G24")
Range("f" & LastRow + 2) = Worksheets("Budget").Range("H24")

Range("c" & LastRow + 3) = Worksheets("Budget").Range("G25")
Range("f" & LastRow + 3) = Worksheets("Budget").Range("H25")

Range("C" & LastRow + 5) = "TOTAL"
Range("C" & LastRow + 5).Select
Selection.Font.Bold = True

Range("f" & LastRow + 5) = Worksheets("Budget").Range("H27")
Range("f" & LastRow + 5).Select
Selection.Font.Bold = True

End With
End Sub
 
B

Bob Phillips

Range("c" & LastRow + 2) = Worksheets("Budget").Range("myG24Name")
Range("f" & LastRow + 2) = Worksheets("Budget").Range("myH24Name")

etc.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
E

Ed

Oh that was quite simple, thanks! and also thanks for the tip, yeah I will
just name what is really needed!

,Ed
 
D

Don Guillett

Naming ranges is great but best not to use names for no good reason. I have
clients pay me to remove them and restore formulas.
 
Top