VBA Fill cells with formula until last row of different spreadsheet

[Milena]

Hi Guys,
I am completely new to VBA and only learning myself from different excel forums.
I have a workbook with 2 worksheets (i.e Sheet1 and Sheet2).
Sheet 1 contains a lot of data and the number of rows will vary from month to month but it always starts in row10. Row 9 is a header and rows 1-8 are always blank.
Sheet 2 contains formulas that refer to data from sheet1. The header is in row 1 and the data starts in row2.
I am trying to write a macro that will fill the formula in the column until the end of data in sheet1.

Let's use simple example: In sheet1 I have a column A. So the data starts in A10.
In sheet2 in column B (starting in B2) I will have a formula =Sheet1A10.

The only code I was able to find:

Sub Test()

Range:("B2:B"&Sheets("Sheet1").Range("A"&Rows.Count).End(xlUp).Row).Formula="=Sheet1!A10"

End Sub

The code only works partially.The problem is, that it always adds 8 additional rows. So for example:
If sheet1 column A:
rows 1-8: blank
row 9: header
row10: 1
row11: 2

Then sheet 2 column B:
row 1: header
row2: 1
row3: 2
row4: 0
row5: 0
row6: 0
row7: 0
row8: 0
row9: 0
row10: 0
row11: 0

I guess this happens because the range in Sheet1 does not specify from which row to start, so it starts from A1 and therefore there is 11 rows.

I tired to modify the code to specify count from A10 but with no luck.

I would really appreciate if somebody could help me on this.

Thank you

Milena

 

[revcanon]

Try this on a copy of your sheet.

Sub test()

'
LastRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
If LastRow = 9 Then
MsgBox "No data to copy.", vbOKOnly
Exit Sub
End If
Sheets(1).Range("A10:A" & LastRow).Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste Link:=True

'
End Sub

If you are actually doing something more complicated than this by adding formulas that do more than point at one cell, let me know.

 

[Milena]

Thank you revcanon.

This will work for most of my columns.
There is however one formula that I will need to use in sheet2- IF. For example IF(sheet1!A10=0,1,A10).

Thank you in advance!

Milena