Copy/paste row with todays date in Sheet2 to sheet 3....but from Sheet 1

I'm trying to create an archive of data, so to speak and I need the data in the row with today's date on sheet 1 to be copied onto my ArchiveData sheet in the next empty row.

BUT, I'd like to be able to link it to a button on sheet ("Master") so that my staff don't have to navigate through the sheets (else it might not get done!)

I think this script will do what I need - I just don't know how to get it to work(or where to put it) when I have a different sheet open.

Please Login or Register  to view this content.

If anyone could help that'd be great!

Your code didn't work.

This does:-

Please Login or Register  to view this content.

You should save this into your personal workbook so it is available whenever excel is open.

The best way is to record a new macro using the macro name you want and specifying that it be on your personal workbook and a short cut key eg e.

Select cell a1 and then stop the recorder. take the steps as if you were going to run a macro but click on edit instead of run.

Paste the BODY of the code inside the recorded macro.

Close the macro window

Save the file

Quit excel, Remembering to Say Yes when asked if you want to save changes to the Personal Workbook.

next time you open the workbook

press Ctrl and e and the Macro will run. If Master and ArchiveData cannot be found the the MAcro will stop.

Thanks mehmetcik, followed your directions (more thanks for being explicit and changed the sheet names to suit ...the module runs through but doesn't seem to do anything, that I can tell anyway. The pasted data should move from Sheet 3 to ArchiveTotals.

Good to know about personal workbooks!

Please Login or Register  to view this content.

Ok here is the problem

when you say sheet3 do you mean what excel understands to be sheet3?

If not then the macro will stop working.

Confused?.....................................Good.

Sometimes when you rename Sheet5 to Sheet3 for example Excel still thinks of it as sheet 5.

So when you ask excel to goto sheet3 it cannot find it, that is an error to my macro quits.

Check Your Names by recording a macro that records you clicking between sheets to see what names Excel has.

Don't believe me, Open Visual Basic, select view project explorer and look at the sheet names.

I will paste the code into a sample document so it works and post that here in ten mins.

Ok

This one works.

You had some of the file names wrong.

I have also added three lines that affect the Archive totals sheet. I think you can work out what they do.

Please Login or Register  to view this content.

Enjoy.

It's alive! Thanks so much! The problem was that I had the sheet names in wrong...awkward.

I so appreciate your help

It's alive! Thanks so much! The problem was that I had the sheet names in wrong...awkward.

I so appreciate your help

Oops, don't know why that posted twice. I thought we had things solved but would it be easy to change the code so that it pastes the values of the cells only...not the formulas/references? I have some columns that are =today() that I don't want to change on the sheet I'm copying to.

Annnd I figured it out myself, again.

added this to the bottom of the script

Please Login or Register  to view this content.