Adjusting Formulas with VBA

[Neltu]

Hello,

Currently i have a macro that copies data to a sheet based on conditions selected on a user form, after the user hits the second command button to view the new sheet, the Macro will paste a specific range that contains formulas below the last data.

The Formulas on the previous sheets were as follows:
=COUNTIF(B10:B124,"N/A")+1
=COUNTIF(B10:B124,"")
=COUNTIF(B10:B124,"*")

These tie into a tracking box that contains the following Formulas that check the range for a specific validation range below.
=COUNTIF(F10:F124,"PASS") and 4 more formulas like this one which look for specific validation answers from a drop down. Validation options are PASS, Fail, N/A. With one formula tracking blank validation cells as well

The problem i have is that they want the final sheet's first column to be deleted for the final outcome, but they still want the formulas to track any changes by the validation. (which of course throws it all off since B column is now A column and makes everything #REF)

Is there a way to adjust the formulas through the macro? Or would i have to manually adjust the formulas each time ? Would it be possible for the macro to update the formulas if the range of rows its looking at is not fixed, but all the columns are (after the initial delete of course)

This is the code i am using to modify the last sheet once the initial macro has been completed, this pastes the range with the formulas, and then deletes Column A.

Private Sub CommandButton2_Click()                                                              ' Done Button
 
   Sheet7.Range("TRACKER").Copy 'This will copy the needed range based on a defined name
   Sheets("Checklist").Activate
   ActiveCell.Offset(1).PasteSpecial 'This will put the named range where it needs to go
   Range("A4").Activate
   ActiveCell.EntireColumn.Delete
 
   Unload Me                                                                                    ' Closes the UserForm, but not the Excel Sheet.
End Sub

I tried just pasting special without deleting the column but it also just returned #REF in all those columns

Thanks in advance for any help you can provide me.