Delete same sheets across all opende workbooks

[G.Bregvadze]

Hi,

I have lots of workbooks, approximately 50, with the same range of sheets... well, some sheets may have a little bit more sheets, but it does not matter, the main thing is that, all of them include several sheets, which have same names across all workbooks.

Could you please, provide with the public code which will enable me to run it as add-in or public macro, and enable me to delete indicated sheets accross all workbooks at once.


Any help will be appreciated, thank yoo in advance.

[humdingaling]

assuming all the files in the same folder?

run macro to read thru all excel files and delete any sheet named "whatever it is"

[G.Bregvadze]

Yes. All the workbook can be located in the same directory. And I need to delete sheets in each workbook., but with indicated names, as each workbook contains sheets with same names, so I need to delete only some of them in each workbook.

[humdingaling]

how do you determine in which workbook which sheet requires deleting?

[G.Bregvadze]

I know their names, for example "sales", "ITM Report" and so on... for exmaple each workbook has 20 sheets, but I need to delete the sheets which will have the indicated names, for example "sales", "ITM Report" etc. Because each workbook have sheets with name names, well may be some of them may include extra sheet, but any way I will need to delete predefined sheets.

[humdingaling]

Please Login or Register  to view this content.

this code will run thru a set directory and open anything with .xls (including xlsx)
can you amend the IF section to you needs

PLEASE make backup of directory first and use temp!! before going "live"

[G.Bregvadze]

How can I include directory there??

my direcotry of workbooks is in my case: C:\Users\cstbregvg\Desktop\Sales reports\xls

[G.Bregvadze]

Works perfectly, thanks...

[humdingaling]

Please Login or Register  to view this content.

as mentioned above in post #6, amend the if section to determine what sheet you delete
currently it just gives you non destructive msgbox
if you put delete code in there the action is irreversible

[G.Bregvadze]

Yes. I understand, I listed there all the sheet names and it only finds them..

[humdingaling]

the code for deleting worksheets is

Please Login or Register  to view this content.

[G.Bregvadze]

Thank you very much. You helped me a lot. Thank you again exactly what was needed.

[humdingaling]

havent tested it but i think you may need a workbook save to stop warning message when you close workbooks
if so
.save
.close

[G.Bregvadze]

Thank you for the support, I did the same, and it works perfectly, thanks a lot...