ateebali

Board Regular
Joined
Dec 13, 2018
Messages
108
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi there. You can pretty much lose all the selects. I'm curious as to why you are deleting the ColorA3 array twice on each sheet? Anyway, try this code:
Code:
[COLOR=#141414][FONT=Consolas][COLOR=#141414][FONT=Consolas]Application.DisplayAlerts = [/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]False[/B][/FONT][/COLOR]
Sheets(Array([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Consolidated Report"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas], [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Welcome"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]))[/FONT][/COLOR][COLOR=#141414][FONT=Consolas].Delete
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas][COLOR=#141414][FONT=Consolas]Application.DisplayAlerts = [/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]True[/B][/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"New Style"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR][COLOR=#141414][FONT=Consolas].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]Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"[/FONT][/COLOR][COLOR=#800000][FONT=Consolas][COLOR=#800000][FONT=Consolas]Garment Detail[/FONT][/COLOR]"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR][COLOR=#141414][FONT=Consolas].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]
' and repeat as necessary for each sheet. [/FONT][/COLOR]
 
Upvote 0
Hello there. I'm sorry I missed the shapes selecter out of each of the lines. the relevant line should read
Code:
[COLOR=#141414][FONT=Consolas]Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"New Style"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/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]Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"[/FONT][/COLOR][COLOR=#800000][FONT=Consolas][COLOR=#800000][FONT=Consolas]Garment Detail[/FONT][/COLOR]"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/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
[COLOR=#141414][FONT=Consolas]
' and repeat as necessary for each sheet.[/FONT][/COLOR][/FONT][/COLOR]
:
 
Upvote 0
Dear Sir, thanks its worked but since sheets have two shapes and both named as "colorA3" after your code, it is only deleting one
 
Upvote 0
OK I wondered why you had 2 deletes. Just repeat the relevant line, so the first 2 would be:
Code:
[COLOR=#141414][FONT=Consolas]Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"New Style"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/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]Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"New Style"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/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]Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"[/FONT][/COLOR][COLOR=#800000][FONT=Consolas][COLOR=#800000][FONT=Consolas]Garment Detail[/FONT][/COLOR]"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/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
[COLOR=#141414][FONT=Consolas]Sheets([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"[/FONT][/COLOR][COLOR=#800000][FONT=Consolas][COLOR=#800000][FONT=Consolas]Garment Detail[/FONT][/COLOR]"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/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][/FONT][/COLOR]
 
Upvote 0
Are you only deleting specific shapes?
 
Upvote 0
Do you have any Sheets that need that ColorA3 shape not deleted? If you're deleting them from every sheet that contain them (or all except one), then code can be much shorter by looping the code through all sheets in a file.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top