Need help to simplify VB code

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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
983
Office Version
2007
Platform
Windows
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]
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
983
Office Version
2007
Platform
Windows
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]
:
 

ateebali

Board Regular
Joined
Dec 13, 2018
Messages
108
Dear Sir, thanks its worked but since sheets have two shapes and both named as "colorA3" after your code, it is only deleting one
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
983
Office Version
2007
Platform
Windows
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]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,665
Office Version
365
Platform
Windows
Are you only deleting specific shapes?
 

ateebali

Board Regular
Joined
Dec 13, 2018
Messages
108
Dear Sir,
Thanks I just put this code twice and its perfectly fine, thanks a lot
 

bukimi

Board Regular
Joined
Apr 12, 2017
Messages
85
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,847
Messages
5,489,229
Members
407,682
Latest member
gmb2521

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top