Convert VBA Code to Formulas in Excel

[dannie]

Basic need is I need my macro turned into formulas or something that can be on the spreadsheet without macros. Work is getting rid of the ability to use macros (disabling all), so I need my codes to work without being codes. I have tried different formulas (find, if, vlookup, etc) and I can't find something that works out completely or easily and user friendly. We are used to having it "easy" with macros, but soon will not have that luxury. I am trying to avoid just copying and pasting and sorting individually (human error is a thing when the spreadsheet is used by a dozen people throughout the day).

What I Want: If first cell in row is value "PD", then cut/paste to sheet named "PD", and then sort by date column/time column.

Any help is appreciated. Thank you in advance!

Example of code below:

If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Value2 = "PD" Then
Lastrow = Sheets("PD").Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(Target.Row).Copy Destination:=Sheets("PD").Rows(Lastrow)
Application.EnableEvents = False
Rows(Target.Row).Delete
Application.EnableEvents = True
End If
End If

Dim ws As Worksheet

For Each ws In Sheets
If ws.Name <> "OS" Then
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("I4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add Key:=Range("J4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A4:Y42")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
Next ws
For Each ws In Sheets
If ws.Name = "OS" Then
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("K4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add Key:=Range("L4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A4:Y42")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
Next ws
End Sub

-dannie

 

[Colo]

Hi,
SORT functions are now available in Microsoft 365. (I don't have that version with me, though.) That function makes it easy to sort the data without using working cells. Better provide us with your information on what version of Excel you are using at work.

Information about SORT function.

SORT function - Microsoft Support

Learn about the SORT function, which sorts the contents of a range or array. SORT is in a class of functions called dynamic arrays.

support.microsoft.com

 

[dannie]

Currently on Excel 2016, will be moving to Microsoft 365 (where macros will not be supported).

We have sheet1 as a template, and fill in information from Column A to Column Y. Whatever word is put into Column A determines which sheet it goes to for processing and then sorting on determined sheet.

 

[Fluff]

Currently on Excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

will be moving to Microsoft 365 (where macros will not be supported).

365 supports VBA if you will be using the desktop app.

 

[dannie]

Thanks for the help. I do understand that macros work with the new Microsoft, but my job going forward is not allowing macros for security reasons.

 

[Fluff]

As you are deleting the PD rows from the sheet, there is no way of using a formula.