VBA help to copy down formulas when new data is added

[msampson]

Hi all
I have a workbook that does some calculations and lookups for me. I've shared it with a few people and simply given them the instructions to copy the last row and paste it down to extend the range of results. But I'd like to share the spreadsheet with many people via a link somewhere (we are about to publish a paper with this calculation and I'm anticipating people wanting access to the automated calculations given their own inputs).
I'd like to set up a template or some sort of protected book so that nobody can change the formulas but that as they input more rows of their data (this goes in columns B, C and D with column A simply being a counter for the number of rows) the cells that refer to those columns will automatically update by copying down the formulas from the above rows.

I have it set up that on sheet 1 (called input) the user inputs their data (B,C,D) and the finished results populate (G,H,I,J). The calculations are done on sheet 2 (called lookup).
I've put a formula in the result cells (columns G,H,I,J) on sheet 1 that are blank unless there is data (=IF(ISNUMBER(B30),lookup!I30,"")) so those formulas need to copy down as more rows of data are added.

On sheet 2 I have columns B,C,D simply say =input!B30 and then columns E-N perform calculations with those inputs. Here there are zeros or error messages showing when there is no input data but that shouldn't matter.

I've been googling to try to figure this out on my own. I've cobbled together this but it's not working

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Column

Case Is = 4

Application.EnableEvents = False

Call MM1

Application.EnableEvents = True

End Select

End Sub



Sub MM1()

Range("G8:J9").Select

Selection.Copy

Range("G" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats

End Sub

When I enter numbers in B, C, D, column E which is a formula saying =C-B automatically populates as does column F which is a warning based upon the values in C and D. But I need G, H, I and J to update and they aren't. The base formulas which are parked in row 8 are not being copied and pasted to the relevant row. Also, I need the second sheet to copy down and that isn't addressed here.

I've never really programmed in VBA, just lots of formulas so this is new territory for me.
thanks, Maureen

 

[Fluff]

Duplicate Automate a spreadsheet

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.