Need help to simplify VB code
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Need help to simplify VB code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Need help to simplify VB code

    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 AsString
    Dim FileName AsString
    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






    EndSub

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    611
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help to simplify VB code

    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:
    Application.DisplayAlerts = False
    Sheets(Array("Consolidated Report", "Welcome")).Delete
    Application.DisplayAlerts = True
    Sheets("New Style").Range(Array("ColorA3")).Delete
    
    Sheets("Garment Detail").Range(Array("ColorA3")).Delete
    
    ' and repeat as necessary for each sheet. 
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    Board Regular
    Join Date
    Dec 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help to simplify VB code

    Sir, I have tried but its not working without ".select"

  4. #4
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    611
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help to simplify VB code

    Hello there. I'm sorry I missed the shapes selecter out of each of the lines. the relevant line should read
    Code:
    Sheets("New Style").Shapes.Range(Array("ColorA3")).Delete
    
    Sheets("Garment Detail").Shapes.Range(Array("ColorA3")).Delete
    
    ' and repeat as necessary for each sheet.
    :
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  5. #5
    Board Regular
    Join Date
    Dec 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help to simplify VB code

    Dear Sir, thanks its worked but since sheets have two shapes and both named as "colorA3" after your code, it is only deleting one

  6. #6
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    611
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help to simplify VB code

    OK I wondered why you had 2 deletes. Just repeat the relevant line, so the first 2 would be:
    Code:
    Sheets("New Style").Shapes.Range(Array("ColorA3")).Delete
    Sheets("New Style").Shapes.Range(Array("ColorA3")).Delete
    
    Sheets("Garment Detail").Shapes.Range(Array("ColorA3")).Delete
    Sheets("Garment Detail").Shapes.Range(Array("ColorA3")).Delete
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,121
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Need help to simplify VB code

    Are you only deleting specific shapes?
    If posting code please use code tags.

  8. #8
    Board Regular
    Join Date
    Dec 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help to simplify VB code

    Dear Sir,
    Thanks I just put this code twice and its perfectly fine, thanks a lot

  9. #9
    Board Regular
    Join Date
    Dec 2018
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help to simplify VB code

    Yes sir

  10. #10
    Board Regular
    Join Date
    Apr 2017
    Posts
    82
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help to simplify VB code

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •