VBA coding for file creation

Hi All,

I am creating a file on a monthly basis where the file has multiple sheets with a data sheet where the data being picked from data sheet with a Sumifs formula in each sheet.

Since im changing the data sheet each month with a new data available for the current month from the system.

Hence i need to copy paste the formula from for (ex) May month cell to June Month cell and replace the name in the formula from DATA_MAY to Data_June.

Can anyone help with a dynamic formula or a coding which will help me in picking up the formula automatically once i pasted the data in the sheet.

Excel Gurus please help me in automating this activity since this takes 4 hours on very month end (4 hours each day from 1st to 5th every month)

Enclosed the file for your reference.

Thanks in advance

Thanks for the response PCI.

Could you please let me know whether the formula SUMIFS can be used in this macro.

Thanks once again.

I would say no! It depends on the version of excel. I thought sumifs started with 2007? If you are running 2003 as per your profile no. then using sumproduct often gave the same result

The final result for me should be based on the reference’s of SUMIFS Function I need the data to be populated without any manual interventions. Since I’m copy pasting all the formulas from previous month to current say for July I will copy the formula from June and paste the same in July column and change the data sheet name as in references. Below is my formula

=SUMIFS(Data_June!$P:$P,Data_June!$R;$R,$A8,Data_June!$B:$B,$E8)

in each excel sheet I will copy paste the formula from previous month and change the reference name which takes more time for me and sometimes I miss some sheets as well.

The final result is what I have attached in the excel

Thanks PCI

Yes the number of rows may vary it might increase each month.

and one more thing the macro does not take the formulas to next column which here is from "June" column to "July" column and the looping does not complete here.

For Each Rg In WS.UsedRange
Rg.Formula = Replace(Rg.Formula, Wkstg1, Wkstg2)
Next Rg

Thanks in advance.

Sorry may be I have put the formula wrongly in the Kannan sheet on first row.

Or please help by replicating (copying the formula in June column and pasting it in July column)

Thanks

Yes it’s running continuously

Thanks a lot and Sorry for the inconvenience caused.

In the input box what should I provide date or month in number

What should we give in input box. Just any date is enough