Copy down a number of rows based on the number of populated rows on another Tab

[gheyman]

I have values going down a column on a tab named "WBS" starting on B8

On another Tab named "WBS_Price_Summary" I want to put a forumula; ActiveCell.FormulaR1C1 = "=WBS!R8C2"
in B8 and copy it down the same number of populated rows on the other tab.

So if I have data in B8 through B23 on tab "WBS" I want to instert the formula above in Sheets("WBS_Price_Summary").Range("B8") and then copy the formula down to B23.

Hope that makes sense.

 

[louisH]

Hi,

Using the AutoFill Function.

nbData = WorksheetFunction.Count(Worksheets("WBS").Range("B8:B10000"))

Worksheets("WBS_Price_Summary").Range("B8").FormulaR1C1 = "=WBS!R8C2"

Worksheets("WBS_Price_Summary").Range("B8").AutoFill Destination:=Range("B8:B" & nbData), Type:=xlFillDefault

Hope it helps

 

[Fluff]

Another option

Sub chk()
Dim Usdrws As Long
Usdrws = Sheets("WBS").Range("B" & Rows.Count).End(xlUp).Row
Sheets("WBS_Price_Summary").Range("B8:B" & Usdrws).FormulaR1C1 = "=WBS!R8C2"
End Sub

 

[gheyman]

Both Worked. Thank you very much!

 

[Fluff]

Glad we could help & thanks for the feedback