Retain all macros and viewing sheets

[PRADEEPB270]

Please refer my attach file.

In this file,there are 4 macros.My problem is whenever I want to run the macro 'CreatMC.9' which is related for 'Dump' sheet only,then, the green tab colour sheets to be deleted and do not appear.These are related to other macro i.e.'PhysicalInventory' and I want to retain the same also.

Can I retain all the 4 sheets and working macros also in a same file?

[Rudi]

If you do not want the green sheets deleted, then you need to remove the loop in the CreateMC9 macro that deletes all sheets except the one called Dump.

Code: Select all

    For Each wsh In Worksheets
        If wsh.Name <> "Dump" Then
            wsh.Delete
        End If
    Next wsh

[HansV]

The macro CreateMC9 contains this loop:

Code: Select all

    For Each wsh In Worksheets
        If wsh.Name <> "Dump" Then
            wsh.Delete
        End If
    Next wsh

This loop deletes ALL worksheets except Dump from the workbook. You can replace the loop with the following:

Code: Select all

    For Each wsh In Worksheets
        Select Case wsh.Name
            Case "Dump", "Original Download", "Main Sheet", "M002"
                ' Keep these
            Case Else
                wsh.Delete
        End Select
    Next wsh

You'll have a problem, however, if you have a storage location beginning with "M002". If so, the CreateSheets macro will try to create a new sheet named "M002" and that will cause an error since the sheet M002 wasn't deleted by CreateMC9.

[PRADEEPB270]

Problems has sorted.Thanks Hans for providing the correction steps.
But Hans,when I run the macro 'createMC.9' then only two green tab sheets retain.I want to retain more sheets after green tab 'Main Sheet'.I can change the sheet name from 'M002' to 'Press Shop' or any other.Now,can it be possible to retain more sheets after 'Main Sheets'?

[HansV]

You can change the line

Code: Select all

            Case "Dump", "Original Download", "Main Sheet", "M002"

to include the names of all sheets that you want to keep.

[HansV]

By the way, if you have an additional question, it is better to post a new reply than to edit an existing reply. I happened to see that you edited your reply by accident, otherwise I would have missed that. If you post a new reply, I will see that.

[PRADEEPB270]

Thank a lot sir.