Help with improving formula

[GA036872]

Hi everyone

I copied the following code from a YouTube tutorial:

The code does as instructed but the workbook is very slow and sometimes crashes. I am using =XLOOKUP formula to retrieve data from another workbook called Mail Merge Data to return a customer's name, address, suburb, and postcode. Can anyone advise how this can be improved/simplified so that the workbook isn't so slow?

Occasionally, we need to paste "DESTROY" not "destroy" in the override column, but I haven't been able to work out how to get VBA to ignore whether the text is upper case or lower case. Furthermore, as I have saved the template and the mail merge data in Sharepoint via Microsoft Teams, Excel makes me keep the mail merge data as a separate tab on the template workbook.

Thanks for your help in advance.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Not my code. This was taken from YouTube
a = Worksheets("Merge").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a
If Worksheets("Merge").Cells(i, 7).Value = "destroy" Then
Worksheets("Merge").Rows(i).Cut
Worksheets("Reject").Activate
b = Worksheets("Reject").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Reject").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Merge").Activate
End If
Next
For i = 2 To a
If Worksheets("Merge").Cells(i, 1).Value = "" Then
Rows(i).Delete
End If
Next
For i = 2 To a
If Worksheets("Merge").Cells(i, 7).Value = "-" Then
Worksheets("Merge").Rows(i).Cut
Worksheets("Send").Activate
c = Worksheets("Send").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Send").Cells(c + 1, 1).Select
ActiveSheet.Paste
Worksheets("Merge").Activate
End If
Next
Worksheets("Merge").Columns.AutoFit
Worksheets("Merge").Rows.AutoFit
Worksheets("Send").Columns.AutoFit
Worksheets("Send").Rows.AutoFit
Worksheets("Reject").Columns.AutoFit
Worksheets("Reject").Rows.AutoFit
End Sub

 

[SQUIDD]

If your happy to have a button, that may suit you better since it seems you are pasting data over to the sheet in bulk.

try the below in a standard module, remove the other code from the sheet module.

run the macro and see if that works better.

Sub sort_my_data()
Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual: Application.DisplayAlerts = False: Application.EnableEvents = False
For a = Range("'sheet1'!A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("'sheet1'!g" & a) = "destroy" Then
        Rows(a).Copy
        Sheets("REJECT").Rows(Sheets("REJECT").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial
        Rows(a).Delete
    End If
    If Range("'sheet1'!g" & a) = "-" Then
        Rows(a).Copy
        Sheets("send").Rows(Sheets("send").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial
        Rows(a).Delete
    End If
Next a
For Each ws In ActiveWorkbook.Worksheets
    ws.Columns.AutoFit
    ws.Rows.AutoFit
Next
Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic: Application.DisplayAlerts = True: Application.EnableEvents = True
End Sub

 

[GA036872]

Hi

So I created a button and assigned the VBA code as suggested. When I tried this at work, it moved all the columns to the other tabs really quickly which is great! Thanks! It does occasionally freeze when I'm first loading up the workbook - but I think this is unavoidable.

There is one issue though: when I saved, exited the file and went back in - it seemed to have deleted the XLOOKUP I had in columns B to F. This means I'll have to type the formula in each time I want to sort data - and as we need to sort data multiple times a day - this isn't practical

Should this be happening? Has this got anything to do with the VBA or is Excel just playing up?

I think I quoted the XLOOKUP earlier in the thread but I'll post it here again for convenience

=XLOOKUP(A2, 'https://sccextranet.sharepoint.com/sites/RAMTeamPlace/Shared Documents/Calc/[Mail Merge Data.xlsx]Mail Merge Data'!$A$2:$A$37810, 'https://sccextranet.sharepoint.com/sites/RAMTeamPlace/Shared Documents/Calc/[Mail Merge Data.xlsx]Mail Merge Data'!$B$2:$B$37810, "")

I then do this for columns C, D, E, and F.

I think one of the main reasons the workbook is so slow is because I have had to use power query to create a table of the external workbook I'm doing the XLOOKUP from.

Would it make the XLOOKUP quicker if I was able to incorporate it into a VBA?

Thanks for your all your help so far - it's much appreciated!