Need help to simplify VB code

[ateebali]

Can someone help to simplify following VB code
If I use something unnecessarily like "Select" etc.

Sub filename_cellvalue()

ActiveWorkbook.Save

For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh


Application.DisplayAlerts = False
Sheets(Array("Consolidated Report", "Welcome")).Select
Sheets("Consolidated Report").Activate
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

Sheets("New Style").Select

ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete


Sheets("Garment Detail").Select

ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete


Sheets("Picture").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

Sheets("Operations").Select

ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete


Sheets("Machines Data").Select

ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete


Sheets("Layout").Select

ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete


Sheets("Report").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

Sheets("Summary").Select
ActiveSheet.Shapes.Range(Array("ColorA3")).Select
Selection.Delete

ActiveSheet.Shapes.Range(Array("Button 554")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 556")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 553")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 627")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Button 555")).Select
Selection.Delete

Sheets("Short").Select
ActiveWindow.SelectedSheets.Visible = False

Dim Path As String
Dim FileName As String
Application.DisplayAlerts = False

If Dir(ThisWorkbook.Path & "\Backup", vbDirectory) = vbNullString Then MkDir ThisWorkbook.Path & "\Backup"
Path = ThisWorkbook.Path & "\Backup" & ""

FileName = Range("O6")
ActiveWorkbook.SaveAs fileName:=Path & fileName & ".xlsb", FileFormat:=50
Application.DisplayAlerts = False


ChDir "C:\Users\ltpurc08\Desktop\Thread Consumption Software"
Workbooks.Open fileName:= _
"C:\Users\ltpurc08\Desktop\Thread Consumption Software\Thread Consumption.xlsb"
Windows("Thread Consumption.xlsb").Activate
ActiveWindow.ActivateNext

ActiveWorkbook.Close






End Sub

 

[ateebali]

Yes need to delete from all sheets

 

[bukimi]

You already got this fragment:

[COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] sh = 1 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]To[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Sheets.Count[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]Sheets(sh).Visible = -1[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] sh[/FONT][/COLOR]

It works on every sheet in a file. Replace Sheets.("name") in code with sh to make:

[COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] sh = 1 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]To[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Sheets.Count[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]Sheets(sh).Visible = -1
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]sh[/FONT][/COLOR][COLOR=#141414][FONT=Consolas].Shapes.Range(Array([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"ColorA3"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])).[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]Delete[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]sh[/FONT][/COLOR][COLOR=#141414][FONT=Consolas].Shapes.Range(Array([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"ColorA3"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])).[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]Delete[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]
Next[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] sh[/FONT][/COLOR]

That way you need that delete only once and it will work even when your sheets got different name.