Change lines in a macro with the same idea but different reordering

K0st4din

Well-known Member
Joined
Feb 8, 2012
Messages
501
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello, everyone,
I have a macro that in the mentioned worksheet, adds new records in two columns if there is no data already entered.
However, right now, it's adding in column B and column D. I'm trying to change this rearrangement slightly, so that when there's an add after ordering for A-Z, but it grabs the whole range of B:E and sorts things again A-Z from column D, not as it is currently separately for D and B.
I made a macro manually, what I want to happen to me, but I don't know how to keep the same actions of the whole macro, but only change the rearrangement.
I would be grateful for your assistance.

VBA Code:
ActiveSheet.Unprotect "k0"
On Error Resume Next
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range

If Target.CountLarge > 1 Then Exit Sub
Set ws = Worksheets("Base")

If Target.Row > 1 Then
  On Error Resume Next
  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If rngDV Is Nothing Then Exit Sub

  If Intersect(Target, rngDV) Is Nothing Then Exit Sub

  str = Target.Validation.Formula1
  str = Right(str, Len(str) - 1)
  On Error Resume Next
  Set rng = ws.Range(str)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub

  If Application.WorksheetFunction _
    .CountIf(rng, Target.Value) Then
    Exit Sub
  Else
*******I think the exact same idea should be changed here, but with the macro below (the arrangement I made)****
    i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
    ws.Cells(i, rng.Column).Value = Target.Value
    rng.Sort Key1:=ws.Cells(1, rng.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom



  End If

End If
ActiveSheet.Protect "k0"

End Sub

Code:
******This is the macro I made myself and I'm trying
to add(change) it to the above macro in its order*******

Columns("B:E").Select
    ActiveWorkbook.Worksheets("Base").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Base").Sort.SortFields.Add Key:=Range("D2:D400"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Base").Sort
        .SetRange Range("B2:E400")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B2").Select


EDIT: Or maybe if it can be changed in the macro itself somehow without adding my idea
 
Last edited by a moderator:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Perhaps I should add, for more clarity:
The macro adds the writing from another two-column worksheet to this worksheet in columns B and D, but when they are added, it then reorders separately for each of the columns in the form A-Z.
And I want to change it a bit, so that when the given word is written and saved in the Base worksheet, the ordering spans from B to D, and when the rearrangement occurs, the one written in D matches the one in B, not each column should be on its own.
I remain available for further clarification.
 
Upvote 0
Hi, anyone with any idea how to get things?
Thanks again everyone!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

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