Macro worked for 2 years, suddenly started giving strange results

Sorry for the poorly worded subject line, I'm not sure how to succinctly word this problem. I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.

The code for one macro is below (the two macros are almost identical, with the exception of saving as different file names):

Please Login or Register  to view this content.

As of two days ago, both versions have started having problems. Everything works perfectly up to the saving as a CSV file. The CSV gets saved, but some content is blanked out (all but one cell of column A, and every cell in column B and C that should have data instead are showing #N/A or #Value). But the .xls file, that is being saved first, has the correct values only data. The other sheets macro has a similar problem, but the csv file is just completely blank.

I've double checked that nothing in the macro has changed since I first put it together. The only thing I can think of that has changed is that a couple of days before the problem appeared we changed the user that is logged in when the macro is run...but it worked fine for this user for 2 days after the change.

Any other ideas of what could be causing this strange behavior? I've rebooted the machine, but after that I don't really even know where to start.

Thanks,
Dylan

*I pieced this together using the recorder and snippets of code found on the web, my level of experience with VBA is pretty close to zero.

I'm not sure why it should stop working. Is the new user doing anything differenly? Maybe ifferent workbooks open. I've tidied up your code, using ThisworkBook in the close o make sure that the workbook containing the macro closes without saving

Please Login or Register  to view this content.

Hi Roy,

Thanks for taking the time to give feedback. The "new user" is just a different domain user that we set up for a couple of reasons, the actual person running the macro has pretty much always been me.

Luckily I think I have figured out what was going on, although I still don't know why exactly. It looks like when the .csv file was saved, only Sheet2was saved, while I had been working in Sheet1. I don't know how there got to be information on Sheet2 of one workbook, or why the macro started saving Sheet2 instead of Sheet1...but the good news is that just removing the extra worksheets from each workbook seems to have fixed the problem.

While I have you hear, I really do appreciate you cleaning up my code, and I hoped you could answer one question about the 2nd to last line you added "Application.DisplayAlerts = True" I assumed this would mean that I would get a prompt when I tried to save as a CSV (which I always get when I manually save as a CSV), but that didn't happen. Can you tell me what this line is doing?

Thanks again.

[s]Edit: Can you also tell me where I can change this thread status to "Solved?" I can't seem to find that option.[/s]

Edit 2: Nevermind, found the how-to to mark as solve. Not exactly what I'd call intuitive...but I got the job done.

Your original code switched off alerts, but didn't return the status to True