Recording macro using cell references in worksheet

Hi, I'm hoping someone brilliant can help. I did a search and didn't see anything along these lines that had already been discussed.

I am working on this project that involves opening several Excel spreadsheets and copying the data into a new workbook. The problem is that the names of the various worksheets I want to copy change every day - it always reflects the date. I open 7 workbooks, go to the sheet named (for example)01.17.07, copy a static range and then paste it to a new workbook.

Is it possible to record a macro and use a cell reference in the destination workbook (the one I want to copy to) to tell it which worksheet to copy? So, for example, I would type 01.17.07 in cell A1 of the new workbook and the macro would look for the sheet named 01.17.07 when running? Then tomorrow, I could type 01.18.07 and it would know to look for a different sheet?

I'd appreciate any guidance. Sorry if this is confusing.

-sara

That makes sense, but can you show me an example of how the code will look when selecting the sheet? Right now, it is a static reference to a sheet called "Previous":

Sheets("Previous").Select

Would I replace "Previous" with "mydate"?

There are times when the date I want to select is not the previous day, therefore typing in the date I am working on is necessary. I assume it will still work if I type the date in A1 instead of using the Today function, correct?

Thanks! That's exactly what I needed.

Seems so simple now...

Can I also use this to insert a variable into a string?

I have this as part of my macro:

ActiveCell.FormulaR1C1 = _
"='C:\Documents and Settings\My Documents\[Workbook.xls]Worksheet'!R125C3"

I want the worksheet name to change based on a particular cell, but when I define the variable and insert it, it returns the variable name in place of the worksheet name. I figured it had something to do with the fact that I'm editing a formula in the macro (replacing a text "=" with a regular "=" so that the cell will return the result of the formula instead of text), but I have no idea how to fix it.

If I use a concatenation, it doesn't return the result of the formula, only the formula itself.

Basically, I did a concatenation in Excel to build the reference I want. I want cells to go to a particular worksheet (named based on a date, so it changes depending on what day I'm working on) to lookup a value. So if I'm working on January 12, I want it to go to sheet 01.12.07 and return a specific cell.

I then took that concatenation and pasted values in the cells where I want the values to display. Obviously, it's pasted as text and the only way to display the formula result is to delete the starting equals sign, replace it and hit enter.

I thought I might be able to write a macro that would do the deleting and replacing for me, but when I do, it builds this code into the macro:

Please Login or Register  to view this content.

I tried creating a variable called MyDate and inserting it to replace the "01.12.07"

Please Login or Register  to view this content.

But when I do that, it doesn't actually insert the value in D1, it pastes it as text and I do not get a formula result - just a formula text.

So anyway, maybe this is just a really complicated way to do something simple, but it's been a long day...

You've been such a huge help already.

Nevermind. Your concatenation did work. I must have made a mistake somewhere when I tested it.

Thanks again for all your help.