Referencing worksheet

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

How do you get this formula to work when the worksheet is not active. i want
the macro pull the formula down but i don't want the user to see the
worksheet that this is all happening on.
Range("f2", Range("a2").End(xlDown)).Offset(0, 5).FillDown
 
Something like:

With Worksheets("Sheet1")
.Range("f2", .Range("a2").End(xlDown)).Offset(0, 5).FillDown
End with

Make sure you put periods in front of all Range and Cell properties, as
shown above.

I normally do a little more work programming, and use object variables, to
make single-stepping (and therefore debugging) easier (untested):

Dim wsSheet1 as Worksheet
Dim rngA2Data as Range
Dim rngFillRange as Range

Set wsSheet1 = Worksheets("Sheet1")
With wsSheet1
Set rngA2Data = .Range("A2").End(xlDown)
Set rngFillRange = .Range("F2").Resize(rngA2Data.Rows.Count)
End With

rngFillRange.FillDown

FYI: You might check out some of the Excel MVP web sites for more help.
http://www.mvps.org/links.html#Excel
 
Thank you. Like you thought i was trying it the way you wrote it but was
forgetting the periods and it wouldn't work. Ha. Thanks.
 
Back
Top