Speeding up a macro that finds information and copies and pastes it.

**DannyJ** · 10-03-2012, 05:39 AM

Hi All,

Hoping you can help me with this one. I have attached the workbook with the code in it. Everything works fine in the macro but as you can see I am no expert with vba! I have manually told it to select every sheet and cell that is needed and therefore the macro runs a lot slower.

A seperate query about the same spreadsheet, this obviously only works on the first column next to the name (as that is what I reference for the cells) so if you wanted a Monday rota it would be fine. Have you any suggestions how I can make this work dependant on what day of the week you want?

For example cell J4 references the day and I thought this code below might do it, but I had no luck!

Please Login or Register  to view this content.

but that just brought errors up in the code. Obviously all of the cell references were changed to 'c' but I have now changed them back.

Of course another way to do it is using the above code to decide which Module to fire and save each day in a seperate Module but I do worry about that slowing the macro down even further!

Any help is much appreciated.

Regards

Danny

**MarkinTX** · 10-03-2012, 09:51 AM

One way to speed it up is to turn off the screen updating before running it,
at the top of your macro add this

Please Login or Register  to view this content.

then turning back on at the end by adding the before the end.

Please Login or Register  to view this content.

<----If this helps you please click the star.

**xLJer** · 10-03-2012, 08:07 PM

If you are interested in reducing the number of lines for IF statements, this will
give you the same results for the variable c:

Please Login or Register  to view this content.

**Jacc** · 10-04-2012, 06:14 AM

I started looking at your code and then I got carried away.

Your code was a mess yes, but at least you tried. Not everybody does that.

See if this thing that I made works for you.

**DannyJ** · 10-04-2012, 07:54 AM

I will always try, I want to learn VBA but stringing a lot of basic commands together seems to be easier to remember. Any tips on how to learn VBA 'more advanced' codes. What I mean is telling it to copy and paste without having to select everything and ones like that.

Thank you to all of you for your help.

I have done stars and solved

.

Regards

Danny

**Jacc** · 10-04-2012, 10:59 AM

Thanks DannyJ! Tips to learn VBA? Well for me it just sort of happened over time. It takes time. If you are interested (check!) and fiddle with it a lot it will happen.
Make small macro's just to test functions and features as you need them.
Actually, make a lot of small macro's to test things out.
Use the macro recorder a lot but make sure you strip that code to the bare minimum before using it, otherwise it is just confusing.
Use things like Debug.Print variableA or MsgBox variableA a lot. It can be used to indicate if a certain part of the code executes. Or type things like ?activecell.address in the immediate window.
Make sure to Tab your code to make it readable.
Learn to use breakpoints (F9) and step through the code (F8).
A book like one of the Walkenbach books on VBA can be a really good help even if things are available on line. Things are usually explained better in books.
Look at other people's code.

I recall when I first took the step to learn VBA one of the first things I got stuck on was the Copy Paste. Now 6 years and a thousand macros later I'm still not sure how to use it. Turns out that it's not very useful feature. I know that if I need it I just google it, run a couple of test macro's and then I'll have it figured out. Until next time...

Other things like application.screenupdating that MarkinTX mentions is a thing to always remember. I didn't use it in Rota, it wasn't needed but I sure use it a lot in my macros in general.

The smart formula of xLJer is not something I would just type in from memory but I have an idea of what is possible and I'll go from there. I hope I remember the basic idea of this one though because that was new to me.

I just recalled now that I did use copy paste at work a couple of weeks ago to copy a range complete with formulas. There it was definitely useful. In your rota application I found no use for it. Look in my code and you'll see.

**DannyJ** · 10-05-2012, 07:38 AM

Yes I did have a skim through your code. It was all linked cells rather than copy and pasting, sometimes the biggest downfall for me is that I think too methodically and go through it step by step which results in more lines of code than necessary! Thanks a lot for taking the time to reply in depth though it is much appreciated. From what I gather from this it is more about understandnig VBA than remembering it. In the sense that if you can understand someone elses code it can be copy and pasted and easily edited to what is necessary.

Thanks again for your time.

Regards

Danny