Copying and Pasting a value into a Range

[Donal28]

Hi All

I,m trying to copy and past a value into a range with the macro below but at the moment I am just getting the value that is hard-coded into the range. Is there a way I can copy a value i.e a date from cell H1 and paste it into the range (H3:H4000) instead of having to hardcode the value each time?


Range("H3").Select
ActiveCell.FormulaR1C1 = "11/2/2011"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H4000"), Type:=xlFillCopy
Range("H3:H4000").Select
ActiveWindow.ScrollRow = 2575
ActiveWindow.ScrollRow = 2564
ActiveWindow.ScrollRow = 2529
ActiveWindow.ScrollRow = 2380
ActiveWindow.ScrollRow = 2081
ActiveWindow.ScrollRow = 1792
ActiveWindow.ScrollRow = 1535
ActiveWindow.ScrollRow = 1267
ActiveWindow.ScrollRow = 985
ActiveWindow.ScrollRow = 697
ActiveWindow.ScrollRow = 394
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 1
Range("I3").Select
With Range("I3")
.NumberFormat = "General"
.FormulaR1C1 = "=RC[-1]-RC[-4]"
.AutoFill Destination:=Range("I3:I4000"), Type:=xlFillDefault
End With
Range("I3:I4000").Select
End Sub


Any help on this would be very much appreciated

Regards
Donal

 

[Andrew Poulsom]

Like this?

Range("H1").Copy Range("H3:H4000")

 

[Donal28]

Yess Andrews thats worked perfectly thanks for your help