Help with Macro (new to macro's)

Copper Contributor

New to recording macro's and I'm a little stuck so after a bit of help please.

 

I'm trying to record a macro that will filter data on one sheet, copy the results (but not the headings) and then paste them in the first empty row in a different workbook. I want to be able to run this each day and the data continue to paste in the first empty row on the workbook. I'd imagine this is very basic but I just can't get the data to paste into the empty row each time.

2 Replies

@Sully_78 

 

I'm not a macro person, so if you're absolutely committed to doing this, you're going to have to wait for somebody else to come along.

 

But I did want to chime in just to say that macros and Visual Basic routines are often overrated. I say that because the built in functions of Excel have continually been extended over the years, often now able to do things that required macros/VBA in the olden days. I know there are other "experts" here at this site who work first to find a non-macro solution if it's at all possible.

 

So if you don't mind describing the actual situation a bit more, or letting us know that you've tried--say--the FILTER function and found it wanting for this or that reason, it's conceivable that somebody here might be able to help you find a different solution.

 

If you're just wanting to learn how to write a macro...go for it, and feel free to ignore my comments. :)

@Sully_78 Assuming you have the Developer tab active and you know how to record a macro, and how to get the Module where the macro gets recorded!

 

Start by recording all the steps that you want to automate. This will result in very crude VBA code, probably containing a lot of useless bits of code due to scrolling, selecting a wrong cell, jumping to another, doing nothing and then selecting yet another cell etc. The recorder literally registers every single step you take, including all your mistakes.

 

Then get rid of all this garbage code and see if you can still run the macro. If there is still too much clutter, or the macro crashes, just scrap it and start all over, trying to make less mistakes.

 

Next would be to Google around to find the bits of code you need to select and filter ranges based on logical rules rather than by cell references. The recorder will have created code that e.g. goes to Sheet1, Selects the Range $A$2:$H$300, copy it and paste in Sheet2 starting at row 510. You need to get rid of these absolute cell references, because next time the data to be copied sits in range A2:H800 or A2:H123. And then perhaps you want to paste it downwards from row 1025 (not 510), being the next empty row at that time. All doable and not very difficult. But if you need help from someone here you would have to upload and example of your workbook.