VBA Code - Improvement if possible

[hrayani]

Hello Friends,

I am using the below code which works fine but I would like you guys to let me know if there is a better way to write it.
Like there are some line in the code which selects some sheets. Is it possible to remove that
or any other improvements if possible last row function instead of .end(xlup) etc

Private Sub update_quality_article_unit()
  
  Dim ws As Worksheet
  
  ActiveWorkbook.Unprotect Password:="2270166"
  
  Set WSArray = Workbooks("REPORTS.xlsm").Worksheets(Array("DATABASE", "Supplier Wise", "Year Wise"))
    
  For Each ws In WSArray
  ws.Visible = xlSheetVisible
  ws.Unprotect Password:="merchant"
  If ws.AutoFilterMode Then ws.AutoFilterMode = False
  Next

   Worksheets("Supplier Wise").Range("B11:D123,B129:D161").ClearContents
   Worksheets("Year Wise").Range("B11:D123,B129:D161").ClearContents
    
ActiveWorkbook.Worksheets("DATABASE").Range("orders_article").Copy Destination:=Sheets("DATABASE").Range("BA4")
ActiveWorkbook.Worksheets("DATABASE").Range("orders_quality").Copy Destination:=Sheets("DATABASE").Range("BB4")
ActiveWorkbook.Worksheets("DATABASE").Range("orders_unit").Copy Destination:=Sheets("DATABASE").Range("BC4")
    
Sheets("DATABASE").Select
    
    'Remove Duplicate Start
    Worksheets("DATABASE").Range("BA4:BC4", Range("BA4:BC4").End(xlDown)).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlNo
    'Remove Duplicate End
    
   'Sorting Start
    Range("BA4:BC4", Range("BA4:BC4").End(xlDown)).Select
    Worksheets("DATABASE").Sort.SortFields.Clear
    Worksheets("DATABASE").Sort.SortFields.Add Key:=Range("BB4:BB4", Range("BB4:BB4").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Worksheets("DATABASE").Sort.SortFields.Add Key:=Range("BA4:BA4", Range("BA4:BA4").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Worksheets("DATABASE").Sort.SortFields.Add Key:=Range("BC4:BC4", Range("BC4:BC4").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Set(s),Pc(s),Pair(s),Dozen(s)", DataOption:=xlSortNormal
    With Worksheets("DATABASE").Sort
        .SetRange Range("BA4:BC4", Range("BA4:BC4").End(xlDown))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   'Sorting End
    
    Range("BA4:BC4", Range("BA4:BC4").End(xlDown)).Copy
    
    Sheets("Supplier Wise").Range("B11").PasteSpecial Paste:=xlPasteValues
    Sheets("Year Wise").Range("B11").PasteSpecial Paste:=xlPasteValues
    
    Sheets("DATABASE").Select
    Sheets("DATABASE").Range("BB4", Range("BB4").End(xlDown)).ClearContents
    
    Range("BC4", Range("BC4").End(xlDown)).Cut Range("BB4")
        
    'Sorintg Start
    Range("BA4:BB4", Range("BA4:BB4").End(xlDown)).Select
    Worksheets("DATABASE").Sort.SortFields.Clear
    Worksheets("DATABASE").Sort.SortFields.Add Key:=Range("BA4:BA4", Range("BA4:BA4").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Worksheets("DATABASE").Sort.SortFields.Add Key:=Range("BB4:BB4", Range("BB4:BB4").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Set(s),Pc(s),Pair(s),Dozen(s)", DataOption:=xlSortNormal
    With Worksheets("DATABASE").Sort
        .SetRange Range("BA4:BB4", Range("BA4:BB4").End(xlDown))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    'Sorting End
    
    'Remove Duplicate Start
     Range("BA4:BB4", Range("BA4:BB4").End(xlDown)).RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
    'Remove Duplicate End
    
   Range("BA4", Range("BA4").End(xlDown)).Copy
   Sheets("Supplier Wise").Range("B129").PasteSpecial Paste:=xlPasteValues
   Sheets("Year Wise").Range("B129").PasteSpecial Paste:=xlPasteValues
    
   Sheets("DATABASE").Select
   Sheets("DATABASE").Range("BB4", Range("BB4").End(xlDown)).Copy
   
   Sheets("Supplier Wise").Range("D129").PasteSpecial Paste:=xlPasteValues
   Range("C1").Select
    
   Sheets("Year Wise").Range("D129").PasteSpecial Paste:=xlPasteValues
   Range("C1").Select
    
   Sheets("DATABASE").Range("BA4:BB4", Range("BA4:BB4").End(xlDown)).ClearContents
   Range("A3").Select
    
    
    For Each ws In WSArray
   ws.Protect Password:="merchant", DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True
     
   ws.Visible = xlSheetVeryHidden
        
   Next
   ActiveWorkbook.Protect Password:="2270166", Structure:=True, Windows:=True
    
    End Sub

Regards,

Humayun

 

[Fluff]

3 refers to the column number .... So if the range is starting from B11 then 3 must be referring to columns B to D
And this is because the copy range is BA4:BC4 > that's 3 columns so the data is pasted from B11:D11 and the way down....

That's right.

 

[hrayani]

If yes then resizing is maintaining the borders and formats etc...

Is this also correct ????

 

[Fluff]

Resize has nothing to do with borders & formats it just resizes a range.

 

[hrayani]

Resize has nothing to do with borders & formats it just resizes a range.

Got it.... Many thanks

Regards,

Humayun

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JLGWhiz]

Thr resize function sets the parameters for a range of cells using a single cell as a refererence point at the upper left of the range. For example, if you want to copy a range 3 columns wide and 12 rows long starting at cell D6, you would write it.

.Cells(6, 4).Resize(12, 3).Copy

locations in Excel are based on a grid network (thus the permanent header at top and left on a sheet) so a user can pass instructions to the CPU that it can understand. Before the advent of the tables (list objects) there was no way for Excel to recognize a location by column or row headers entered by the user. But even now with that capability, the grid system still has to be used to define cell and range addresses.

 

[hrayani]

Thr resize function sets the parameters for a range of cells using a single cell as a refererence point at the upper left of the range. For example, if you want to copy a range 3 columns wide and 12 rows long starting at cell D6, you would write it.

.Cells(6, 4).Resize(12, 3).Copy

locations in Excel are based on a grid network (thus the permanent header at top and left on a sheet) so a user can pass instructions to the CPU that it can understand. Before the advent of the tables (list objects) there was no way for Excel to recognize a location by column or row headers entered by the user. But even now with that capability, the grid system still has to be used to define cell and range addresses.

Thanks dear

I am still on learning stage.... with the help of experts like out here