Count rows in another sheet and apply formula

Question

I have an excel workbook that has 2 sheets. Sheet1 & Sheet2

Sheet1 will have a variable number of rows in it. In sheet2 i am looking to apply a formula in column "A" and apply it all the way down as applicable based on the number of rows present in Sheet1.

If i use the following code within the context of the same sheet then it works but i need to move this to another sheet.

With Sheets("Sheet1")
    .Range("O2:O" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=A2&""|""&B2&""|""&E2&""|""&G2"
End With

Change "Sheet1" to "Sheet2", and then put Sheets("Sheet1") before .Cells. — BigBen
Give this guy a medal! I have been trying to figure this out all day and finally decided to submit for help. Thanks this resolved exactly as i needed. — Shawn Baird

BigBen BigBen · Accepted Answer · 2020-03-03T20:06:12

To close this question out: Change "Sheet1" to "Sheet2", and then put Sheets("Sheet1") before .Cells (and to be consistent, before .Rows too):

With Sheets("Sheet2")
    .Range("O2:O" & Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row).Formula = "=A2&""|""&B2&""|""&E2&""|""&G2"
End With

Count rows in another sheet and apply formula

1 Answers