How can I pull date from multiple cells from multiple worksheets, or an advanced find and replace

Question

Hello I am just trying to get into a bit of macro programing to help my Mom, and Dad get some excel work done, and they have some ungodly amount of paper work in excel to get together for tax time.

I would like to learn how to write a macro that can ether pull info from several cells in multiple worksheet, and place them into separate cells and columns for each set of cells like so.

[ Invoice number ][ Customer ][    Date    ][ Total ]
[      #1146     ][   Name   ][ 01/19/2014 ][  000  ] <--each cell here if from a different worksheet
[      #1147     ][   Name   ][ 01/21/2014 ][  000  ]
[      #1148     ][   Name   ][ 01/30/2014 ][  000  ]
[      #1149     ][   Name   ][ 02/01/2014 ][  000  ]

Or one that is an advance find and replace that can find and replace in sequence. So I could tell it to look for the number in a set of selected cells and replace that number with another number but go up by one for every row.

It would do this

Basically I need it to find and replace the file name like this

--> from this [=[1150.xls]Sheet1'!$C$8] to this [=[1151.xls]Sheet1'!$C$8]
    in each row, and go up by one in each row.

So if you could point me to some resources that can get me started or give some general code that I could play with to learn from that would be great.

Thanks for any help even if it is to go some where else.

Answer 1

After six years I finely leader how to write a macro for LibreOffice to do the find a replace I needed for my parents spreadsheets. You can find it here. https://github.com/LuxBrush/Invoice-number-find-and-Replace-macros/tree/v1.0.0-beta

Answer 2

it sounds like your planning to do something the hard way.

A mix between vlookup, match ,index and pivot tables will do this kind of work.