How to make macro dynamic?

[Samanthad2007]

I have a workbook that calculates three different things for each quarter (so 3 calculations for Q1, 3 for Q2, etc.). The formulas are currently stored in the first row of each quarter and when new data is pasted in each quarter, the macro autofills the formulas down for the current quarter only. I got this to work, but I only have the code set up through Q4 2022 so once it is 2023, I will need to edit the code manually (example below shows the Q3 code with the 3 calculations in columns BX, BY, BZ). How can I create a macro that will work for years on end? I've tried creating a drop down menu, where the user selects the current year and current quarter, but that only works for as long as I set up the code for. I hope this makes sense!

Public Sub Q3_2020()

Application.ScreenUpdating = False
'Q3 2020
With Sheets("Data 1")
.Range("BX2").AutoFill .Range("BX2:BX" & .Cells(Sheets("Data 1").Rows.Count, "A").End(xlUp).Row)
.Range("BY2").AutoFill .Range("BY2:BY" & .Cells(Sheets("Data 1").Rows.Count, "A").End(xlUp).Row)
.Range("BZ2").AutoFill .Range("BZ2:BZ" & .Cells(Sheets("Data 1").Rows.Count, "A").End(xlUp).Row)
End With
'Q3 2020
With Sheets("Data 2")
.Range("BX2").AutoFill .Range("BX2:BX" & .Cells(Sheets("Data 2").Rows.Count, "A").End(xlUp).Row)
.Range("BY2").AutoFill .Range("BY2:BY" & .Cells(Sheets("Data 2").Rows.Count, "A").End(xlUp).Row)
.Range("BZ2").AutoFill .Range("BZ2:BZ" & .Cells(Sheets("Data 2").Rows.Count, "A").End(xlUp).Row)
End With

End Sub

 

[Wookiee]

Without being able to see your actual workbook, I'm doing a lot of guessing, but here's what I would suggest (I use a similar method with one of my monthly/yearly reports and it allows me to update the pertinent formulas with the click of a mouse).

Assuming each sheet is a template for Quarterly reporting, it sounds to me like you have the values in Row 1 and then are copying the predefined formulas from BX2:BZ2 down to the last row with data.

First, I would set up a sheet named Template and fill it with the common formulas. I would place a macro button and map it to the following macro:

Sub SamanthaD()

Dim lngLastRow   As Long

With ActiveSheet

   lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
   
   .Range("BX2").AutoFill .Range("BX2:BX" & lngLastRow)
   .Range("BY2").AutoFill .Range("BY2:BY" & lngLastRow)
   .Range("BZ2").AutoFill .Range("BZ2:BZ" & lngLastRow)

End With

End Sub

Every time you need to create a new Quarter's sheet, copy the Template worksheet and rename it to reflect the quarter on which you wish to report. After you've pasted the data, click the macro button and it will update your formulas.

 

[TotallyConfused]

Hello Samantha

I'd like to WELCOME you to this site. It is a fantastic place and for me has proven to be a gold mine of information. I hope you enjoy your stay here and will become an active participant. Isn't programming fun, especially when it doesn't work?

If you're not sure why your code will only work for the quarter you have coded it for, it's because in your code you specify which sheet the computer is supposed to work with. For example, in your first example, you specify "Data 1" sheet. Your code will crash if you try to work with any other sheet but that one. You will notice that in the code Wookiee gave, no specific sheet is mentioned except the 'ActiveSheet'.

I would like to draw your attention to something in your code that you might want give some thought to in the future if you are working with large sheets. In each example, you calculate the number of rows of data three times. If you have only a few rows of data, this probably isn't a big deal as Excel does this quite rapidly. However, if you had a large number of commands that depended on the number of data rows or a large data file, then you may notice a definite slowdown in processing time. This is the reason Wookiee calculated the number of data rows once and assigned that value to the variable 'lngLastRow'. Just make sure this calculation is OUTSIDE of any loop. You can use any variable name you wish. This value can now be used any number of times by simply referring to that variable without having to re-calculate it. A side benefit is that it makes the commands shorter and easier to debug.

Good luck,
TotallyConfused