GA036872

New Member
Joined
Jul 8, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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


1657312113902.png
 
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.

VBA Code:
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
 
Upvote 0
Solution

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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! :biggrin: 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 :confused:

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 :)

Excel Formula:
=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.

1657658685387.png


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!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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