VBA: copy formula after last row

Pantoffsky

New Member
Joined
Jan 26, 2016
Messages
12
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:

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!
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
What should happen for the value from SOC report sheet? Should that be I2 then I3 etc?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,105
Messages
5,622,762
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top