VBA: copy formula after last row

[Pantoffsky]

Hi All,

Every month I need to gather a bunch of data and deliver it combined to my manufacturer. I'm trying to automate it a bit, but I'm a bit stuck. In the sheet named Work, there is a lot of data, but the amount of lines is different every month. After this data, I need to add a couple of rows from different sources. The content of these rows is created by a formula and also here, the amount of rows is different every month. So what I did now is select the first empty cell in column A and copy the formula in it:

Sub addRows()
Worksheets("Work").Activate
Dim ws As Worksheet

Set ws = ActiveSheet

For Each cell In ws.Columns(1).Cells
    If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell

ActiveCell.Formula = "=""     "" &  'Report SPE'!G2 &""                "" &'Report SOC'!I2"

End Sub

My question is... how do I get this formula flexible? So, if there are 10 rows in Report SPE, create 10 rows in worksheet Work? And the cell should also be +1, so Report SPE'!G3 on the next row, Report SPE'!G4 on the next... etc.

Suggestions are welcome!
Many thanks!

 

[Fluff]

What should happen for the value from SOC report sheet? Should that be I2 then I3 etc?

 

[Pantoffsky]

Yes, it should always be the same number as SPE, so always +1

 

[Fluff]

Ok, how about

Sub Pantoffsky()
   Dim UsdRws As Long
   
   UsdRws = Sheets("Report SPE").Range("G" & Rows.Count).End(xlUp).Row
   Sheets("Work").Range("A" & Rows.Count).End(xlUp).Resize(UsdRws - 1).Formula = "=""     "" &  'Report SPE'!G2 &""                "" &'Report SOC'!I2"
End Sub

 

[Pantoffsky]

Yeah!

Thanks Fluff!!

 

[Fluff]

You're welcome & thanks for the feedback.