Only run code in macro if condition is met?

[OLLY-7]

Hi all,


So have a macro that is copying a worksheet (a Purchase Order template) so that I can automate making copies of said template and changing the PO no. in the new copy. See below...

Please Login or Register  to view this content.

That is all working fine and pretty straightforward up to that point.


However what I would now like to do is add some code to this macro so that it will only action the copy and create new PO code for those PO numbers listed on a sheet I already have called 'Control Sheet' that lists the actual PO sheets required.

That sheet looks like this:

Header in A1 'Sheet Name' and in B1 'Print?
and then down column A = A2: 'PO 1' A3: 'PO 2' etc. etc.
and down column B = B2: 'PRINT', B3: 'PRINT' etc. etc.

A B
Row 1 Sheet Name Print?
Row 2 PO 1 PRINT
Row 3 PO 2 PRINT
Row 4 PO 3 PRINT
Row 5 PO 4 PRINT
Row 6 PO 5 PRINT





Any help will be gratefully received.


Thank you very much in advance!



Oliver

[xladept]

Post Deleted - Tags Added.

[OLLY-7]

Apologies and thank you for the notification.


Duly corrected.

Hope all good now.


Regards

[AlphaFrog]

Change the name of your PO 1 sheet to PO Template. The code will copy PO Template and rename the copy as PO1 (just like the other PO Names) if it is in your Control Sheet list.

Please Login or Register  to view this content.

[OLLY-7]

Thank you very much AlphaFrog!

That works perfectly!

Thank you!


I did also have a macro that was deleting all sheets from 'PO 2' to 'PO 15' (when I was using PO 1 as my 'template').

However that doesn't appear to be working any more - I guess because now there are likely to be less than 15 PO sheets at any one time.

Could you kindly suggest another macro to delete any sheets starting with 'PO' / or that are listed on the control sheet?

Or perhaps an extension to the code you provided above so that whenever it is run, it auto deletes any pre-existing PO sheets?


Thank you very much again and kinds regards,


Oliver

[AlphaFrog]

Please Login or Register  to view this content.

[OLLY-7]

Thank you.


Again - works perfectly.

Thanks again and best regards,


Oliver