SaveCopyAs

[tmkkoservo]

I have the following code, which will save a copy of the workbook as a given name and the current date and time when a value of 1 is moved into a cell. The code seems to work as I expect it to. I would like to refine it to save a copy of certain sheets in the workbook instead of the entire workbook, and name and store the copy the same way it is in the existing code. This way the user opening the copies isn't promted to update external data. Can anyone Help? Thanks in advance...

******************

Please Login or Register  to view this content.

*****************

[Greg M]

Hi there,

I don't think it's possible to save individual sheets of a workbook, but what you CAN do is to create a new workbook containing only the sheets you need & then save that workbook.

To do this, replace your code:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and insert the following subroutine in your VBA module:

Please Login or Register  to view this content.

Note that the statement:

Please Login or Register  to view this content.

contains the names of the sheets you want to include in the new workbook.

The routine works by creating a new workbook, deleting all of its worksheets except one (a workbook must contain at least one worksheet) & then renaming that worksheet with a unique "rubbish" name (just to avoid any conflict with the sheetnames in your source workbook.)

The routine then copies the specified worksheets (in the specified order) from the source workbook to the new workbook, and then deletes the worksheet with the "rubbish" name.

Finally, the new workbook is saved using your existing naming convention & is then closed.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

[tmkkoservo]

Greg,
Thanks for your quick responce. The code below is what I have now. It works great, but will only run one time. If I open the workbook and put a value into a cell which causes cell H1 to result in a value of 1, the code runs and saves a copy just like we want it to. If I return to the sheet with the code and toggle the H1 cell value back to 0 and 1 agian, the code does not appear to run.

Thanks, Kevin

Please Login or Register  to view this content.

[Greg M]

Hi again Kevin,

Two errors - a stupid one on my part & another (not so stupid?) one on yours.

Mine first. The very last part of my code should read:

Please Login or Register  to view this content.

The previous version had EnableEvents set to False - quite wrong!

On your side, the first line of your code should read:

Please Login or Register  to view this content.

This ensures that the routine is triggered whenever a cell value is CHANGED - the "WorkSheet_Calculate" event is triggered whenever the worksheet is RECALCULATED - just changing a cell value may not force a recalculation.

Anyway, hope the above helps - thanks for your feedback.

Regards,

Greg M

[tmkkoservo]

Greg,
If calling me stupid is the price I have to pay for your programming advice, then Fire Away! If I would have paid more attention, it would be pretty obvious that events won't work unless they are enabled. So, now it works! Thank you. And I used the Worksheet_Calculate event because the data is populating the cell from an OPC server. I tried the Worksheet_Change first, but it will only work if the cell is selected and changed. Thanks again...

[Greg M]

Hi again Kevin,

Glad to hear that all's working fine now & happy to have helped out.

Many thanks for the feedback & best regards,

Greg