Some useful macros that I've accumulated

[trisoldee]

Hello everyone,

Since I'm still pretty novice at creating macros but wanted to find a way to contribute to the forum, I figured that I would post a few of the different macros that I've come across in the hopes that someone else might find them of use. Some of these are a product of members of this forum replying to my requests for help and some are from other searches that I've done.

Enjoy,
Tristan

[mikeTRON]

It would be helpful for you to list what macros you have and what they do, so people can search for them and whatnot.

While we are on the subject, I found these macros helpful as well:
http://chandoo.org/wp/2013/11/19/sec...acro-workbook/

[FDibbins]

That looks very impressive, Tristan, thanks for sharing

[trisoldee]

Do you mean post each one as an individual thread? That definitely makes more sense for people to actually be able to find them. I'll get on that this evening.

[trisoldee]

That looks very impressive, Tristan, thanks for sharing

Just so everyone knows, all I've done here was compile the work of others that I've found to be extremely useful in my day to day. I don't want to take any credit for this. Everyone on this forum has been amazing and all the credit should go to the moderators and everyone else that actually knows what they're doing.

[mikeTRON]

Do you mean post each one as an individual thread? That definitely makes more sense for people to actually be able to find them. I'll get on that this evening.

No I meant include a summary of all the macros so this thread will be searchable and will come up when people search in the future. Does that make sense?

Or just paste it to the forum and break each piece of code out INTO code so it looks pretty.

[mikeTRON]

Something like this:


Macro to shade alternating rows grey to make a list a little easier to read.

Please Login or Register  to view this content.

Macro will merge multiple workbooks to a single sheet in a new workbook, a browser window will come up so you can select the files you want. I’ve used this when merging information in the same format sent in from various sources.

Please Login or Register  to view this content.

This one basically does the same thing as above only it will put each worksheet on it’s own tab in the new workbook.

Please Login or Register  to view this content.

And this one will merge all of the worksheets in a workbook to a single worksheet.

Please Login or Register  to view this content.

Someone on the forum just sent me this one because the one that I had was a giant mess. This will print the first worksheet in all of the selected workbooks. A browser window will come up to let you select the files you want.

Please Login or Register  to view this content.

This macro will copy the active worksheet but will allow you to specify the number of copies that you want.

Please Login or Register  to view this content.

This will run text to columns using | as the delimiter through all of the sheets in a workbook.

Please Login or Register  to view this content.

A final item that I find myself using constantly is that when you want to include an auto fill in your macro you need to change what the recorder produces to something like this or the range will only copy the data as far as it did when you recorded it.

Please Login or Register  to view this content.

[mikeTRON]

I like this one to change all the fields in all of my pivot tables from COUNT OF to SUM

Please Login or Register  to view this content.

[john55]

It's a good idea, thx trisoldee! I,ve seen a file having a lot of examples, I am sorry if I wrong but it had posted by TMS...

[trisoldee]

To use paste special as values without having to touch your mouse.




Sub pastevals()
'
' pastevals Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub