Copy paste formula up to a cell using VBA

[Angeliki]

Hello all

I have a little problem and i need help

I have two worksheets. In the first one i manually add historic data (every
time different size).
In the second one I have formulas based on the historic data.
I want to add the formula only in the first row and then using a button to
copy paste
the formulas up to the cell that the historic data ends. I used this code
but the problem is that
i do not know how to define B? which every time will be different one

Range("A1:B1").Select
Selection.AutoFill Destination:=Range("A2:B?"), Type:=xlFillDefault
Range("A2:B?").Select
Range("A1").Select

A1 and B1 contains the formulas
So if for example in Sheet1 i have historic data from A1-A500 (the size
varies )
I want formulas in Sheet 2 to be calculated up to cell B500

I am really stuck with this one.

Thanks in advance

AngelikiS

 

[Dave Peterson]

Can you pick out a column that always has data in it?

If yes, then I'd use something like:

Option Explicit
Sub test()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
.Range("a1:b1").AutoFill _
Destination:=.Range("a1:b" & LastRow), Type:=xlFillDefault
End With
End Sub

If you can't rely on a column, you might be able to use
..specialcells(xlcelltypelastcell). But excel keeps track of that a lot better
than most humans. You might be underestimating where ctrl-End takes you.

Option Explicit
Sub test()
Dim LastRow As Long
Dim dummyRng As Range
With ActiveSheet
Set dummyRng = .UsedRange
LastRow = .Cells.SpecialCells(xlcelltyplastcell).Row
.Range("a1:b1").AutoFill _
Destination:=.Range("a1:b" & LastRow), Type:=xlFillDefault
End With
End Sub

sometimes using .usedrange will reset that lastused cell to what you expect.

 

[Dave Peterson]

typo alret!

xlCellTypeLastCell is misspelled in the second sub.

LastRow = .Cells.SpecialCells(xlcelltyplastcell).Row

<<Snipped>>