'Use this code to export all modules from Personal.xlsb file to another workbook. Prior to running macro, open target workbook and change its name in the code accordingly.
Sub ExportImportModule()
For Each Module In Workbooks("Personal.xlsb").VBProject.VBComponents
If Module.name <> "ExportImportModule" Then
Module.Export ("c:\temp\" & Module.name & ".bas")
Workbooks("TargetWorkbook.xlsm").VBProject.VBComponents.Import ("c:\temp\" & Module.name & ".bas")
End If
Next Module
MsgBox "All objects (modules, forms and classes) were copied!"

This is solution to post at: http://excelexperts.com/macro-code. Example file is attached.

Solution to: http://excelexperts.com/budgeting

Solution to: http://excelexperts.com/comparing-data-sets

Thanks "Mr Spreadsheet" John Walkenbach for this formula.

When using particular macro often, it is handy to have it on right mouse-click. In this example there is a simple macro "Show Date and Time", and it was added to right mouse-click menu.

Supposing you have a macro called "DateAndTime" in a "Module1", copy code from attached .TXT file to the "Workbook" part in VBE.

With slight modification you can add more commands to the right mouse-click menu. It was explained in the comments.

If you want custom commands available on right mouse-click, regardless of the file you work on, copy the code to your "Personal" macro file.

This is an example based on post at: http://excelexperts.com/brackets-in-formulas

Bracketed column name can be seen when you choose "Show Total Row" with Data Table. When you right-click on Data Table and choose "Show Total Row" and choose "SUM", this is what you get within a Total cell. "Meal" within brackets refers to column "Meal" within Data Table, not a named range. So, "SUBTOTAL(109,[meals])" means "SUM of 'Meals' column", where "Meals" is a column within Data Table.

Maybe "Sum" cell was copied away from a Data Table, and that is why it looked strange.

This is solution to: "Hyperlink Macro that converts a list of sheet names to hyperlinks to the sheets", at: http://excelexperts.com/hyperlink-macro-converts-list-sheet-names-hyperl....

To create an index page with hyperlinks to all sheets in a workbook:

1. First, insert a new sheet and name it "Index"
2. In VBE (Alt+F11), select "Index" sheet and paste the code from the attached .txt file into the right pane:
3. Run Macro "CreateIndex"

Solution to count before change at: http://excelexperts.com/data-counting

I guess that you want to count number of occurences of "Yes" and "No" in a block of consequtive occurences (i.e. before it changes from "Yes" to "No" or from "No" to "Yes"), right?

Having your data in column A, enter 1 in B2 and this formula in B3: =IF(A3=A2,B2+1,1).

Thus, when "Yes" turns into "No" or vice versa, counter resets to 1. If it is same as the row above, it increments counter to + 1.

Example file attached.

Please, let us know if this is what you need.

This is solution to request posted at: http://excelexperts.com/custom-date.

Hi,
1. Enter 2 Mar and 8 Mar 2014 into A1 and B1 as dates
2. Format them like: [$-409]Ddd d mmm yyyy"
2. Enter this formula in A2 and fill it down:

=PROPER(TEXT($A$1+7*(ROW()-1),"[$-409]Ddd d mmm yyyy")) &" to " & PROPER(TEXT($B$1+7*(ROW()-1),"[$-409]Ddd d mmm yyyy"))

Example file attached.

If you need a list of distinct/unique items from the range (column A) where items appear more than once, select a range of the same size (column C) and enter this as an array formula (press CTRL+SHIFT+ENTER):

=IFERROR(INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:" & ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:" & ROWS(Data))))),"")

Where "Data" is a named range containing original list (column A).

Named range is not mandatory, but I recommend you create it, so formula is easier to handle.

Solution to How to display values of 2 variables which range from 4 to 15 in 1 message window using MsgBox? at: http://excelexperts.com/msgbox-question. This generates a pair of random numbers from defined range, so two numbers in a pair are always different. Thanks to J. E. McGimpsey for this code.

Check example file attached.

Regarding: "http://excelexperts.com/listbox-and-list" and: "http://excelexperts.com/more-macros-listbox-items": How change in listbox value calls a different macro.

Create ActiveX ListBox and create a list of macros, like in the example file attached.

Put this code into your worksheet part:

Private Sub ListBox1_Click()

If ListBox1 = "Macro1" Then
Call Macro1
End If

If ListBox1 = "Macro2" Then
Call Macro2
End If

If ListBox1 = "Macro3" Then
Call Macro3
End If

If ListBox1 = "Macro4" Then

This is reply to the post: http://excelexperts.com/add-incremental-numbers

Having your column filtered, you probably insert incremental numbers in different column, right?
1. Filter your data by column A
2. Enter the first value in column B, for example: 100
2. Select the remaining cells in column B to be filled
3. Press F5 and choose "Special", select "Visible Cells Only"
4. In the first empty cell in column B type = (select cell with 100 entered ) + 1
5. Press CTRL+ENTER

You will get 100, 101, 102 etc. in column B, only in visible rows.

Screenshot attached.

If you need information on computer name, username logged on, Operating System , domain name, "Windows" folder, "Application Data" folder, number and type of processors or alike, this is for you. This macro lists all environment variables in column A of active sheet, starting from cell A1.

Example file attached.

Solution for: http://excelexperts.com/return-top-5-values-based-criterion

In brief: you need Category List re-arranged, additional column in "Average Rank" sheet and named ranges for newly inserted column and all months respectively.

In detail: I re-arranged Category List and made a new one on sheet "Category_New".
Then I inserted a column on sheet "Average Rank" containing type (Composite, Standalone etc.).
Named ranges were created for march and april 2013 (containing values).

Finally, array formula calculates Top 5. Example for February 2013 - "Composite":

You might get a list with names and surnames stored in the same cell, and you need to reverse their order (from "Name Surname" to "Surname Name"). Most common way to achieve this is through MID/LEFT/RIGHT functions or "Text to Columns" command. But that is only the first step. Next step is to concatenate cells in reverse order.

Here is a formula to get it at once, and it also works with two surnames and two names, separated by space (Caetano Gonzalez) or dash (Mary-Jane).

Here is a macro to insert image files into respective cells, according to image filenames or full filepaths.
File names/paths are stored in "D" column, and pictures are inserted in "A" column, in the same row.

If you have only the list of file names, use the first macro ("Short Name"). Change the macro so it refers to the folder where image files are stored.

If you have a list of image files as full path+filename, use the second macro ("Full Path").

I recommend using the second method.

Although there is a more elegant way created by Nick at: http://www.excelexperts.com/Compare-2-Lists, here is one more way.

Let's suppose there are two ranges with some common values. In order to find what values from one range exist in another range and vice versa, use the following formula in the cell beside A2 cell in the first range:

=IF(COUNTIF($D$2:$D$10;A2)>0;"Exists in Range Two";"Nope")


If you don't know it already, you can write your formulae (the complex ones) in a way similar to one that programmers use to make their code more legible. It is particularly usefull when creating complex nested formulae (formula within formula), as well as the logical ones, like OR, AND and alike. Why? Well, it is very easy to get lost in long formula: Where is the beginning? What is condition? Where to put a parenthesis? Where did I make a mistake? etc.

Look at the following formula and try to figure out what it does:

=IF(DAY(AT$11)=1;INDIRECT("Volumes!"&ADDRESS(ROW();COLUMN());TRUE);AVERAGE(INDIRECT("Volumes!"&ADDRESS(ROW();COLUMN()-(DAY(AS$11)));TRUE):INDIRECT("Volumes!"&ADDRESS(ROW();COLUMN());TRUE)))


When data entry is strictly defined, it is nice to have data validation list in place, so entries are uniform. Furthermore, a nice way to make data entry faster is to have "smart lists", or dependant lists. What does this mean?

When you choose an option from drop-down list in a column, you need only possible options listed in another column, based on the first choice. Let's look at a simple example with dogs and cats.

Example file is attached.

Sometimes, while working on large workbooks with many complex and slow formulas we set calculation to manulal instead of automatic. From time to time, one needs to calculate only a range of cells. However, pressing F9 (Calculate) calculates all cells, and that can often be time-consuming.

To calculate the selected range of cells only (and to save significant amount of time) when calculation is set to manual, select a range of cells and use a simple macro, like this:


Sub CalculateSelection()
Application.screenupdating = False ' speed up execution by preventing screen flickering
Selection.Calculate ' calculating selected cells
Application.screenupdating = True ' reverting to default settings
End Sub


This macro changes case of selected range. No additional column or UPPER/LOWER/PROPER function necessary.
When you select a range of cells or single cell, run macro and - when prompted - enter "L", "U" or "P" (lower, upper, proper).
Selected cells contents is changed accordingly.

The first version was criticised with reason. Meanwhile, I used INDEX/MATCH logic to create a function similar to VLOOKUP.

If you are fed up with VLOOKUP inability to work leftwards, here is add-in called Two_Way_Lookup. It returns value from the same row, no matter where it is stored: in the column to the right or to the left from lookup value.


Public Function TWO_WAY_VLOOKUP(Lookup_Value As Variant, Reference_Column As Range, Result_Column As Range) TWO_WAY_VLOOKUP=WorksheetFunction.Index(Result_Column,WorksheetFunction.Match(Lookup_Value, Reference_Column, 0), 1)
End Function


It is based on INDEX/MATCH. This is for users who don't like nesting formulas. It works like this:

Secondary Axis tutorial explains how to show two data series on different scales.
To see it, please, follow this link: http://itpainless.webs.com/01_Start.htm.