Need Help with Macro to Automatically Save a New Sheet with Data Every Day

Hi there! I haven't worked with Excel Macros for a few years so please bear with me.

We have a data logger that logs temperature points for a specific piece of equipment. We have a pre-existing Excel macro that puts those data points into a table, and then into a chart. It records a new data point every minute.

What we'd like is a macro that can automatically take all of the data points for a day (so, 1,440 points) and save them into a new workbook with the date as the name. Preferrably at midnight every night. We would leave Excel running on a network computer 24/7.

If there's a way to do this automatically at midnight, without needing to click a button, please let me know. Thank you for all of your help, we really appreciate it!

Hi, thank you for responding! I didn't receive notification that you had responded again so I assumed that nobody else had replied after me, but I see now that I was wrong. Thank you for replying!

Also, I will be sure to enclose the code with the code tags, thank you for that information as well.

Here's what I have so far:

Please Login or Register  to view this content.

You have the right idea but I think you are confused about how to structure code. Compare your code to the correction below. I have also added a function to your file name that will include a date stamp in the name. You can modify the format of the date to suit your needs.

Please Login or Register  to view this content.

This code creates a kind of a loop in time; first you call ScheduleTimedSave then every time the save is performed it reschedules itself to run again. However, something has to run it that very first time. To do this automatically, you could add the following code to the ThisWorkbook module, which kicks things off as soon as the workbook is opened:

Please Login or Register  to view this content.

6StringJazzer, thank you SO MUCH for your help! That code worked beautifully last night and saved all of the data perfectly.

Here's what I have so far:

Please Login or Register  to view this content.

My next problem is how to clean the master sheet so that it can start collecting the data again? The master sheet has other macros running in the background to collect the data from a data logger. I need this save macro to clear the sheet so that the new information is saved in the same spot as the previous information, does that make sense?

The excel sheet already has a button that runs a "clear sheet" macro, and it also has a "start logging" macro. The data won't be collected until the "start logging" button has been clicked.

I'd like the save macro to immediately clear the sheet, and then click the "start logging" button so that it begins to collect data immediately again. Is that possible? I tried recording a macro to do it but it keeps getting hung up when I click one of the buttons.

Thanks again for all of your help, you've been amazing!!

Originally Posted by angieinohio

The excel sheet already has a button that runs a "clear sheet" macro, and it also has a "start logging" macro. The data won't be collected until the "start logging" button has been clicked.

I'd like the save macro to immediately clear the sheet, and then click the "start logging" button so that it begins to collect data immediately again. Is that possible? I tried recording a macro to do it but it keeps getting hung up when I click one of the buttons.

This is fairly straightforward but we are starting to get into specifics of your workbook. Can you post the code that is run when you click the "start logging" and "clear sheet" buttons? In macros, you cannot write a macro that actually pushes a button, but you can simulate it by calling the button handler. To give you the right instructions I would have to see whether you have old Form buttons or ActiveX buttons; the code is different for the two styles. The best approach of all would be to simply post your entire file.

Hello!

Here's the code to clear the sheet:

Please Login or Register  to view this content.

And this is the code to start logging:

Please Login or Register  to view this content.

Thank you again so so so much!

I would modify the code you provided thus, to simply call the button click handlers.

On reflection, there is also a better way to actually save the file. VBA has a "SaveCopyAs" function that is cleaner than the cut & paste into a new workbook. It is also unnecessary to change the current directory when using the entire path name. This also avoids a problem: When you SaveAs, the active workbook is the one you just saved, and if you clear it and start logging again, and for any reason hit Save, you end up wiping out the one you just saved. When you do SaveCopyAs, you save a copy that is set aside and the current workbook does not change.

On seeing your code, I am very curious as to how the cmdClearSheet_Click procedure continues logging once every minute, and what causes logging to start such that you have to start it again.

Please Login or Register  to view this content.

Hi again!

Ok, so should I copy and replace my entire code with the code you provided above? I do like the idea of SaveCopyAs, but I'm assuming it's a different code, right?

Here's the code for clearing the sheet:

Please Login or Register  to view this content.

And here's the code to start logging again:

Please Login or Register  to view this content.

Thanks again!

Replace your code (as you showed in post #7) in its entirety with the revised block I provided in my most recent post (post #10). You do not need to change anything about the code you have that clears the sheet and starts logging.

Hello again!

Ok, so I replaced my code with the one you provided in post 10, but when I try to run it it gives me an error that says "Compile Error: Sub or Function not defined" and then highlights the "cmdClearSheet_Click" part. Also, there is a highlighted yellow arrow pointing to the heading "Public Sub PerformTimedSave()"

So here's what my code looks like right now:

Please Login or Register  to view this content.

Ok, so then I tried adding the two button macros to the master macro (making one long one) and here's what I got:

Please Login or Register  to view this content.

However, when I try to run that then it gives me an error stating "Compile Error: Sub or Function not defined" and highlights the "SetDefaults" title from the Clear Sheet button section. It also highlights in yellow the "Sub cmdClearSheet_Click()" title.

What am I doing wrong? Thanks again for your help, you've been amazing!

Can you attach your file?

I'm not sure what you're asking, attach the entire excel file? The code in post 14 is the entire code that I have.

Yes, attach the entire file. The code you posted does not give a clue to the errors you are describing. I think there is more code in the file than you have posted because of references to components that appear nowhere in the posted code, such as

Please Login or Register  to view this content.

When you draft a reply you can click on the paper clip icon in the set of edit controls to attach a file.

Ah, thank you for the clarification. Unfortunately my employer doesn't want me to upload the file because it might compromise our security, is there any other way I can show you the problem?

The original file was running fine (the save as new sheet file). Then when I tried to add the two commands to clear the sheet and start logging again there was trouble. The buttons still work fine if I just click them in the sheet. So why can't I copy and paste the entire code from the buttons into the original code for the save? Is it in the wrong order somehow?

It seems as though the buttons need to be clicked physically, since it states to "cmdClearSheet_Click," right? Is there a way to remove the need to physically click the button and just run automatically after it saves the sheet? Or am I way off base here?

I'm really happy with the progress we've already made (and thank you so so so much!), and these last couple of things are the final piece of the puzzle before we can save the code and start running it on the equipment full time. Thank you again for all of your help.

I did find the following code in reference to the RHTemp:

Please Login or Register  to view this content.

Also, there was this:

Please Login or Register  to view this content.

And this:

Please Login or Register  to view this content.

And finally this:

Please Login or Register  to view this content.

Do any of those help? Thanks again so much for your help!!

Originally Posted by angieinohio

It seems as though the buttons need to be clicked physically, since it states to "cmdClearSheet_Click," right? Is there a way to remove the need to physically click the button and just run automatically after it saves the sheet? Or am I way off base here?

In Excel VBA, think of Excel as being a process that is running all the time, and it allows you to run your VBA code. If you click on a button, the Excel process says, "Hey, somebody clicked a button called cmdClearSheet. Do I have some VBA code to run when somebody clicks that?" So it looks for a Sub called cmdClearSheet_Click. But you can also call the same Sub anytime you want, and it does exactly the same thing that would happen if you physically clicked the button. That's what I directed you to do in your code.

However, you are getting a compile error on the call to SetDefaults. That's why I wanted to see your code, because I cannot understand how SaveAsNewSheet ever worked in the first place. Even with the additional code you posted I do not see where SetDefaults is defined.

We are kind of at an impasse without my being able to see your file. I don't want to press the issue but if you deleted all the data and formatting from all the worksheets I would still find it useful to have the code in its native habitat, and your employer may find it acceptable. If not then I'm stuck.

Hi again!

Ok, I've attached the original file. This is from before I added any of the save macros and whatnot. Please let me know what you think.iLog Blank.xls

The code in the file you attached doesn't compile because RHTemp1 is not defined. I can't figure out what it is, although it may be a class related to data communication for collecting the readings. It has the following attributes:

Visible (Boolean)
ActiveXCommandMode (Boolean)
TimerInterval (Long)
StartContinuousReadings (Sub)
ActiveXIPPort (Long)
GetReadings (Sub)


So I can't get too far here. But I'll add that I can't see anything that would interfere with your ability to successfully use the code that you modified based on my suggestions. Everything is defined in places where it is visible to other components that need to see it. I'm sorry to say that I'm stumped. Maybe someone else will visit this thread and recognize a problem that they know the solution to.

There is a section that says this:

Please Login or Register  to view this content.

Is that what you're looking for to define it?

The method you are using is the next best thing to SaveCopyAs although without knowing exactly what error you are getting I can't speculate on what might be wrong.

As for RHTemp1, the code you show defines a value for it but it's commented out. Further, I don't see it declared anywhere and the code has Option Explicit which requires declarations. That's why I got a compile error. Based on the usage in the code you show, it's an Object, which means it could be almost anything.

Anyway, I'm happy that you made so much progress, best of luck!

Hello again!

I'm in need of your help! I thought everything was working great, when I ran the macro it would save the new workbook and everything just fine, but it seems like it's having trouble restarting itself at midnight.

I tried testing it last night and when I came in this morning this is the error message it gave me: "Cannot run the macro ....PerformTimedSave. The macro may not be available in this workbook or all macros may be disabled."

Do you know how I can fix this? I know the macros weren't disabled because it was able to run the other ones, and that macro is definitely saved in the workbook's code. I'm just not sure where to look to fix this.

Thanks again for all of your help!

I didn't mean to strand you but I've never had this happen. The only thing I can imagine is if the workbook was closed after the first time the macro ran, but Excel continued to stay open.

Hi Any1 help on the below

Sheet1 Sheet2
name data1 data2 name
raja 556 556 RAMESH
ravi 422 265 SURESH
kaja 122 222 KAMESH
ramesh 45 25 vimalesh

Now i need to to delete the entire data from sheet1 if the name from sheet 2 found in sheet1.


Please help

Hello again! And thank you for responding!

I'm not sure how that could have happened. I left the workbook open overnight while I was gone and it was still open when I came in. If I run it from Excel by clicking on the macro and running it it runs perfectly, but for some reason, if I try and leave it overnight that's when it runs into the problem.

Do you know where I should start looking for the problem, or do you have any troubleshooting tips? The end is so close I can taste it!!

I am running a test of the code I provided to see if I can reproduce your problem. If I don't get the same problem, the only way I can diagnose it is to use your same file, which you have said is an issue. I'll post later tonight.

OK, here's the problem. The code I provided in post #2 has to be put into a Module. I am assuming you have instead put it into the code for a Sheet. If you move it into a Module (or create a new Module) you are good to go.

See my attached working model for how it should look. Timed event repeated daily.xlsm This version runs every 30 seconds, and simply logs the fact that it runs. To stop it you must close Excel (not just the file, you have to close Excel).