Here is exactly what I am trying to do through VB in Excel:

Weekly data pull fills colums A:G. Row count is always different. I am
modifying the data pull through VB, and I have a VLOOKUP formula in cell H2.
What I want VB to do is copy that formula down column H to the last row (with
data) each week. I guess I want it to be dynamic so that as rows
decrease/increase the formula is only copied down to the final row/record.

I know someone out of this smart group will know how to do this!

Thanks in advance!

Tony

Hi,
Try this, hope it works for you.
Sub Macro1()
Range("G1").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row
Range("H2").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A" & (LastRow - 2)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub

Thanks,

"Tony P." wrote:

Here is exactly what I am trying to do through VB in Excel:

Weekly data pull fills colums A:G. Row count is always different. I am
modifying the data pull through VB, and I have a VLOOKUP formula in cell H2.
What I want VB to do is copy that formula down column H to the last row (with
data) each week. I guess I want it to be dynamic so that as rows
decrease/increase the formula is only copied down to the final row/record.

I know someone out of this smart group will know how to do this!

Thanks in advance!

Tony

Hy Tony

You can try this macro

Sub test()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("H2").AutoFill Destination:=.Range("H2:H" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

It will fill the formula in H2 to H last cell in column A


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tony P." <Tony wrote in message ...
Here is exactly what I am trying to do through VB in Excel:

Weekly data pull fills colums A:G. Row count is always different. I am
modifying the data pull through VB, and I have a VLOOKUP formula in cell H2.
What I want VB to do is copy that formula down column H to the last row (with
data) each week. I guess I want it to be dynamic so that as rows
decrease/increase the formula is only copied down to the final row/record.

I know someone out of this smart group will know how to do this!

Thanks in advance!

Tony

Worked like a charm, David! Thanks!!!!! Have another one for you when you
get a second. Subject will be "Delete Entire Row"

"David" wrote:

Hi,
Try this, hope it works for you.
Sub Macro1()
Range("G1").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row
Range("H2").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A" & (LastRow - 2)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub

Thanks,

"Tony P." wrote:

Here is exactly what I am trying to do through VB in Excel:

Weekly data pull fills colums A:G. Row count is always different. I am
modifying the data pull through VB, and I have a VLOOKUP formula in cell H2.
What I want VB to do is copy that formula down column H to the last row (with
data) each week. I guess I want it to be dynamic so that as rows
decrease/increase the formula is only copied down to the final row/record.

I know someone out of this smart group will know how to do this!

Thanks in advance!

Tony