Copy formulas x number of times based on a cell value

[gsupike]

What I am trying to do is have a row of formulas copied down by a set number of cells, which I have managed to do in VBA with:
Sub CensusCopy()
Range("A2:D2").Copy Range("A3").Resize(1 * Range("Company!$B$3").Value - 1)
End Sub

What I would like to accomplish is having those new formulas delete if the new value is less than the previous value. So, if the previous number was 100 and the new value is 50, then only 49 (plus original) would display. I would also like to have the module run automatically once the cell value is changed.

Thanks in advance for your help!

 

[Joe4]

Welcome to the Board!

What is the name of the sheet the range A2:D2 range you are copying exists on?
Is the number in cell B3 on the "Company" sheet being updated manually, or by formula?

 

[gsupike]

A_Census is the worksheet and Company!b3 is updated manually. I've figured out how to use a button to clear and copy formulas using the code below (still a work in progress), but I'd still like it to run automatically if possible:

Sub ClearCensus()
Worksheets("Census").Range("Census!A3:D5001").ClearContents
Worksheets("A_Census").Range("A_Census!A3:ED5001").ClearContents
Worksheets("A_BuyCensus").Range("A_BuyCensus!A3:BU5005").ClearContents
Worksheets("Employee").Range("Employee!A7:N5007").ClearContents
Worksheets("Employer").Range("Employer!A9:M5009").ClearContents
Worksheets("BuyUp").Range("BuyUp!A7:k5007").ClearContents
Range("Census!A2:D2").Copy Range("Census!A3").Resize(1 * Range("Company!$B$3").Value - 1)
Range("A_Census!A2:ED2").Copy Range("A_Census!A3").Resize(1 * Range("Filter!F1").Value - 1)
Range("A_BuyCensus!A2:EU2").Copy Range("A_BuyCensus!A3").Resize(1 * Range("Filter!M1").Value - 1)
Range("Employee!A6:N6").Copy Range("Employee!A7").Resize(1 * Range("Filter!F1").Value - 1)
Range("Employer!A8:M8").Copy Range("Employer!A9").Resize(1 * Range("Filter!F1").Value - 1)
Range("BuyUp!A6:K6").Copy Range("BuyUp!A7").Resize(1 * Range("Filter!M1").Value - 1)
End Sub

 

[Joe4]

If that code does all that you need, then we can easily add automated code that will run that whenever cell B3 on your Company sheet is updated.

Just go to your Company sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor that pops-up (this procedure MUST be named as shown below and MUST be in this Sheet module in order to run automatically):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then ClearCensus
End Sub

 

[gsupike]

Thank you for your help!

 

[Joe4]

You are welcome.
Glad I was able to help!