Creating a Macro that copies a date from one spreadsheet and moves it into another

Question

Hi there,

I have no experience with developing macros but hope you may be able to help.

We have 12 excel spreadsheets that monitor the distribution of documents.

Each time a document is distributed, the date that document was distributed is entered into its relevant excel spreadsheet.

What we would like to do is create a seperate excel spreadsheet that finds the latest distribution dates for each of those 12 documents and list each date in one sheet.

The dates in each of the 12 documents are listed across the row. i.e | MAR-10 | JUL-10 | SEP-10 |

The macro will have to find the last date in the row and then move it to the new document.

The new document will hopefully look like this:

| Sample 1 | SEP-10 | | Sample 2 | AUG-10 |

Really hope you can help!

Thanks, Nathan

Answer 1

Assuming your dates are all in column A in your sheets couldn't you do a simple formula instead of a macro? I maybe misunderstanding whether these are separate workbooks or worksheets.

couldn't you use =MAX(Sample1!A:A) =MAX(Sample2!A:A) etc. On your summary worksheet?