Autofill Formula when data is added to the row

Trundlethegr8

New Member
Joined
Jan 26, 2022
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Good morning,

This is my second post so apologies if i don't include all the required info.

I have a workbook that tracks customer expenditures. The summary worksheet lists all customers and displays the current financial data. Each customer has an indidivual worksheet that tracks the daily data for historical reference. I currently have gotten some assistance to create a macro that copies the data from the Summary sheet and pastes on the individual worksheet if column A matches a worksheet name.

I have formulas on each individual customer worksheet that calculates various differences on columns P through S. When the new data is pasted from the Summary sheet, is there a way to copy/paste the formula from the row above the new data?

Apologies again if this doesnt make sense.

Here is what i currently have.

Sub Copy_Rows_To_Sheet()

Application.ScreenUpdating = False

Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
Dim ans As String
With Sheets("Summary")
For i = 2 To Lastrow
ans = .Cells(i, 1).Value
Lastrowa = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
.Rows(i).Copy: Sheets(ans).Rows(Lastrowa).PasteSpecial xlPasteValues, Transpose:=False
Next
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Exit Sub


End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

gordsky

Well-known Member
Joined
Jun 2, 2016
Messages
530
Office Version
  1. 2016
Platform
  1. Windows
assuming it is a consistent formula all the way down the column, that i refers to the first row the formulas are stored in and columns p to s contain the formulas you want copied down try the below after your 'End With' statement

VBA Code:
Sheets(ans).Range(sheets(ans).cells(i,16),sheets(ans).cells(Lastrowa+1,19)).filldown
 

Forum statistics

Threads
1,175,488
Messages
5,897,691
Members
434,673
Latest member
kktfc

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