If a cell already has a value, do not run the macro?

**jond291** · 09-11-2014, 04:05 AM

I have a macro which populates cell "J6" with a new report number every time the spreadsheet is opened, and another macro which saves the document to a new spreadsheet using the value in cell "J6"

Once the spreadsheet has been saved using the value in cell J6, if i open the newly created excel document named after cell J6, it generates another number and overwrites the previous value in cell J6.

This is my code:

Please Login or Register  to view this content.

Is it possible to modify this, so that if there is already a value in cell J6, it will not run?

Thanks

**zbor** · 09-11-2014, 04:15 AM

Try

Please Login or Register  to view this content.

**jond291** · 09-11-2014, 04:19 AM

Thanks for the reply but it doesn't work, when I open the newly created spreadsheet it still replaces J6 with a new number.

**zbor** · 09-11-2014, 04:21 AM

Can you upload example workbook?
Make sure to remove confidential informations.

**jond291** · 09-11-2014, 04:38 AM

Ok, I've uploaded it.

Just so you know the way ti works is:
Open the spreadsheet and it runs a macro to find the most recent file in a directory.
Another macro which removes the .xlsx extension of the file
Another macro which then adds 1 to the file, so it's the next report number
And finally another macro which copies the new value into cell J6

The save button then saves the sheet based on the value in cell J6

P.S. The code may be a bit messy, there is a lot of different things happening, and I've tried many things to get it to work, I'm just not sure what I can and can't delete anymore.

**zbor** · 09-11-2014, 05:00 AM

Maybe you want to put it in Module4:

Please Login or Register  to view this content.

**jond291** · 09-11-2014, 05:05 AM

Unfortunately that doesn't work either, would it be possible to achieve the same results in a different way, maybe by telling it to save as a read-only?

**zbor** · 09-11-2014, 05:07 AM

I really don't know what you try to achieve and when you just answer "that doesn't work" doesn't help me to understand further problem

**jond291** · 09-11-2014, 05:16 AM

Ok sorry, i'll try to be more detailed, this is what happens when I make the change your suggested:

I click the save button, which saves the file as a new document named after the value in cell J6.
When I open the newly created document, I want it so that the value in cell J6 is still the same as when it was saved.
However due to the macro which updates the cell every time the document is opened, it keeps changing to a different number.

I need the macro which updates J6 (In the master spreadsheet) to not run once the file has been saved.

I hope this is clear enough as I really appreciate your help.

**zbor** · 09-11-2014, 05:24 AM

Then you want just delete macro on Workbook open?
So it won't change when it's opened?

**jond291** · 09-11-2014, 05:29 AM

Yes but only on the newly created spreadsheet, which is created using the save button, I still need the macro to run on the main spreadsheet. (The one I attached)

**romperstomper** · 09-11-2014, 06:08 AM

Do you save the main workbook with J6 populated?

I'd suggest you use a template file rather than a workbook, personally.

**jond291** · 09-11-2014, 06:11 AM

Originally Posted by romperstomper

Do you save the main workbook with J6 populated?

I'd suggest you use a template file rather than a workbook, personally.

The main workbook is saved without J6 populated, but when you open it, J6 is automatically populated. I then click save and it saves to a new file called whatever is in J6. However it is once I open this new workbook that I do NOT want the macro to run, as it currently overwrites what ever was in J6.

**romperstomper** · 09-11-2014, 06:56 AM

Then, as zbor already suggested I think, you simply change the Workbook_Open code in the ThisWorkbook module to something like this:

Please Login or Register  to view this content.

**jond291** · 09-11-2014, 07:52 AM

Yes!!

That's It!

Not sure why zbor's didn't work, the 2 answers are slightly different so must be something different in there somewhere.

But it's working

Big thanks to both of you, really appreciate it.

If a cell already has a value, do not run the macro?

LinkBack

Thread Tools

Rate This Thread

Display

If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Re: If a cell already has a value, do not run the macro?

Thread Information

Users Browsing this Thread

Similar Threads

How to call a macro when clicking in a specific cell from you personal macro workbook?

[SOLVED] Macro Delete Contents in 23rd corresponding cell if the cell in range is a non-blank cell

macro to run a macro for evrey cell in column depending upon valuein cell

Macro that uses a cell reference in a cell to activate that cell and copy it to the cell

Cell Rounding Macro - significant digits is variable from cell to cell

Bookmarks

Bookmarks

Posting Permissions