Basic Questions about Excel

lshaw52

New Member
Joined
Dec 8, 2016
Messages
4
Hello, I am new here because I am interested in learning more about Excel. I also want to ask general questions about the situation I am in to see if it's possible to do what I am trying to accomplish.

Everyday I run a set of reports to send out to my team that include about 8 different reports. All of them have filters to do so by date and other information, they also have formulas. Someone sent my manager a macro for most of the reports he runs, and now that I have seen it I want to teach myself how to do it the way he did. His macro for starters is a button that he created, and all you have to do is hit it and it runs the macro's. Another thing is, he created an actual xlsm file with his macro and it includes more than one report and once you hit the button it runs them all at the same time. Can anyone point me in the direction for me to teach myself how to do this?

Another thing is when I filter the dates during the recording of the macro, once I run it the filter selects a different parameter of dates. I know that's alot, but thanks in advance for the help.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Welcome to the forum!

The answer depends on many factors. When working towards a goal like that, do it in steps. You will get more help for simple threads than a whole project. You can always reference a thread for the next step towards the final goal.

Let's start with a simple task first:
The command button can be a Form object or ActiveX. Right click the ribbon, customize, and add the Developer tab. You can then Insert either control. For ActiveX controls, doubleclick the control to start coding the Click event in the Sheet object. Right click Form controls to Assign/Create a macro in a Module object.

Recording a macro will teach you a lot. Once you get it recording, you will eventually want to modify it to be more efficient and dynamic. e.g. Select and Activate are seldom needed for most projects.

Sounds like an AutoFilter will be something that you will need to work with. AdvancedFilter is another course to consider.

Later, you can work on exporting Worksheet(s) to a pdf file maybe. Either the pdf file or Outlook file can be attached to an Outlook email. There are lots of examples for how to do these tasks. Much will depend on your data.
 
Upvote 0
Sorry, I definitely figured that this would be a loaded question. I guess the first thing I should work on it creating a macro for each of the reports I run daily and take it from there. Thank you.
 
Upvote 0
I would suggest that the easiest way to do this is with the macro recorder.

1. make sure you are very familiar with ALL the steps you need to take. Practice a few times (manually) so you know exactly what you need to do.
2. on the bottom-right of the excel screen, you should see a small square with a red dot on it (kinda looks like a small worksheet). That is the RECORD button. Once you press that, EVERYTHING you do will be recorded in a macro and can be repeated again.
3. here we go. Press the Record button. You will be asked to name the macro - name it something that explains what it does (keep the name shortish and don't use spaces, use underscore _ if needed)
4. do ALL your steps (carefully), everything you do is now being recorded. This includes applying (and removing) filters, printing, saving etc
5. When you are done, click the RECORD button again, and you are done.
6. To make the macro available to use, you could either put it on the ribbon as Ken suggested, or just put a shape on the sheet and "link" the macro to that - it will then become a macro "button"
7. Select a shape (stay basic or get fancy - up to you) and put it on your sheet...size as needed.
8. Right-click into the shape and click "assign macro" Pick your macro
9. RTR-click again and click Edit Text - give it some meaningful text
10. SAVE your file under a different name (just to make sure it all works right), then test the macro to make sure it all works well
 
Upvote 0
Thank you. This is what I have learned so far, I am currently recording all of my macros and putting them in the same file. The question I have now is, can I use a variable filter in my macro. For example, if I run a report everyday that provides data for the last 14 calendar days, the dates will change when I run it tomorrow. Is the macro smart enough to pick that up, or will the dates be messed up going forward?
 
Upvote 0
There may be a way to reference the dates, but you cannot do that through recording. (my VBA skills are weak at best). You would (I think) need to get into the code and add the references there
 
Upvote 0
Got ya, I have created a macro with this information, and I am going to run it again tomorrow and see what happens. Honestly, since I am so new to this I might just create to macro to provide me with the bare report and I can filter from there.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top