How to make macro dynamic?

Samanthad2007

New Member
Joined
Aug 12, 2020
Messages
2
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Wookiee

Active Member
Joined
Nov 27, 2012
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top