count rows

[SMILE]

Hi
I have two worksheets - sheet1 and sheet2.
Sheet1 is my main data which contains 7 colums (A-G) and number of rows
varies depend on data.
In sheet2 I have formula in Row1 (A1:G1) and I need to copy down the
formula to exactly the same number of rows in Sheet1. (For eg: if my
sheet1 has 15 rows, I need to copy the formula in A1:G1 of sheet2 till
A15:G15).
Hope someone help me
Thanks in advance
Toms

 

[John P]

Toms

You could add an unused row with a number or letter in it and then count
that separately each time with countif?


John

 

[Dave Peterson]

How do you know the number of rows in Sheet1?

I used the last used cell in column H in my example:

Option Explicit
Sub testme()

Dim toWks As Worksheet
Dim fromWks As Worksheet
Dim LastRow As Long

Set toWks = Worksheets("sheet1")
Set fromWks = Worksheets("sheet2")

With toWks
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
.Range("a1:g1").FormulaR1C1 = fromWks.Range("a1:g1").FormulaR1C1
.Range("a1:g" & LastRow).FillDown
End With

End Sub

 

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm