Hello! I am fairly new to macro creation and am currently trying to streamline a reporting process I use that requires monthly inputs of data. I am trying to set it up so that I can pull specific row ranges of data from a master worksheet based on the date (specifically, for an entire month, mm/dd/yyyy form).
I have attached a example of the workbook i am working with. Sheet1 is the master sheet of data, and Sheet2 is the specific month I am looking to have the data transferred to via a VBA Macro button. Most of it is simple copy and paste, with some data moving to different columns (the re-order is important for when Sheet2 is used to input data into the online reporting system). I have also included an example version of how i am planning to have Sheet2 look after performing the macro.
I am looking to get a macro setup that searches the dates on the master sheet and pulls data based on the month, and then copies and pastes based on those corresponding dates (and thus columns) to the monthly sheet. If the macro could ask for "which month would you like to generate a report?" and have user input the month (i.e. Jan, Feb, etc)
Some additional functions I am looking to have in the macro:
- Need to have an average taken of the "treated Fe" and "treated Mn" values from the weekdays (Monday to Friday) and have that average inputted on the corresponding Friday under "treated Fe (avg)" and "treated Mn (avg)"
- Under the "Clearwell volume" and "CT required" columns, would be nice to have the form auto fill those in with a set value that doesnt change (400 and 10, respectively)
- Under "Treated flow M3/hr average", need to get this value form the Sheet1's "Treated Water" by taking that Sheet1 value and then dividing it by 24 (hrs/day) before it pastes it to Sheet2
If anything is unclear, I will try to elaborate in more detail! Thank you for your help!
I have attached a example of the workbook i am working with. Sheet1 is the master sheet of data, and Sheet2 is the specific month I am looking to have the data transferred to via a VBA Macro button. Most of it is simple copy and paste, with some data moving to different columns (the re-order is important for when Sheet2 is used to input data into the online reporting system). I have also included an example version of how i am planning to have Sheet2 look after performing the macro.
I am looking to get a macro setup that searches the dates on the master sheet and pulls data based on the month, and then copies and pastes based on those corresponding dates (and thus columns) to the monthly sheet. If the macro could ask for "which month would you like to generate a report?" and have user input the month (i.e. Jan, Feb, etc)
Some additional functions I am looking to have in the macro:
- Need to have an average taken of the "treated Fe" and "treated Mn" values from the weekdays (Monday to Friday) and have that average inputted on the corresponding Friday under "treated Fe (avg)" and "treated Mn (avg)"
- Under the "Clearwell volume" and "CT required" columns, would be nice to have the form auto fill those in with a set value that doesnt change (400 and 10, respectively)
- Under "Treated flow M3/hr average", need to get this value form the Sheet1's "Treated Water" by taking that Sheet1 value and then dividing it by 24 (hrs/day) before it pastes it to Sheet2
If anything is unclear, I will try to elaborate in more detail! Thank you for your help!
