Pantoffsky
New Member
- Joined
- Jan 26, 2016
- Messages
- 14
- Office Version
- 2019
- Platform
- Windows
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:
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!
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:
VBA Code:
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!