Dragging formula down - Excel 2016

Hi,
I have a problem with dragging down formula in VBA. I will set ranges dynamically in the end, but even when setting range manually Excel doesn't work correctly. I'm using Excel 2016. Here is my code:

Please Login or Register  to view this content.

P.S.: These lines are somewere in code where I allready create a new sheet from copying previous one, and size changes in new one, so I need to manually enter formulas again. Lines are out of any loops etc., so It should work.


I'm not sure If I missed something out, so for any help thanks in advance !

If you use tables to store your data, then formulas will automatically fill down the rows.

I'm afraid that doesn't work for me, let me explain a bit....

I have a month sheet, with CF for weekends and SUM formula at end column for all cells. And I have a button for generating new sheet - this code creates new sheet with next month. And since months doesn't have same number of days, I have to add/substract columns, I'm doing this by copying Active sheet and then properly add/delete columns. Unfortunally, by doing copy, all cells have same formulas as they were in copied sheet, so I need to change cell references. I've done for CF, but this part still causes me problems.

Can you attach a sample of your workbook and the code?

Sure, here It is. Open module and search for "CHECK THIS LINES". I also have a code in Workbook_change, but I comment that out. I hope language won't be an issue, I don't have time to translate all.

I've not looked at the code, but my first thought is why complicate it by removing or adding columns for the different numbers of days in the month?

Have your template be a constant 31 days and just don't display the date in the columns if they are outside the sheet's month.

ie. Feb would have 31 columns and the last 3 day's dates would be blank or greyed out using conditional formatting.

Just a thought.

Yes, indeed, I was thinking about that too in first place. However, complete sheet is being copied and sent via E-mail, and our end users are unsatisfied with design like this.