Macro needed to sort report

David117

New Member
Joined
Apr 8, 2023
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hello Wise Ones
I have to complete a weekly report using information provided by our computer system. This report has to have the same columns removed each time and then have the results filtered into groups.
Hoping that someone could provide a macro so that when the information is pasted into the excel sheet I could press a button to activate the macro, removing the unwanted columns and then filter and sort in the next sheet


Information received: Columns A to BX with defined amount of Rows
Information would be copied and pasted into new sheet cell (A3)


Information only needed from columns: A, B, C, D, V, AF, AG, AP, AQ, AX, BC, BD, BM, BM, BN, BO
The information found in column AP would be how the rows are grouped together - only 12 possible options

Hopefully someone can help as this is waaaaayyyyyy beyond my limited understanding
Let me know if you need any further information concerning the information that the macro would need to work with
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
If you record a macro doing all of the deleting it will enable you to rerun it each time you get fresh data.
As to the grouping, if the sizing is different each time, but there are only 12 possibilities, 1 option would be to get the unique names in the field, find the start row by using Match and finding the size by using Countif. Then using the range(Match number, match number + Countif number -1).group repeating for each name.
If the grouping is too much, At least start by recording the deleting and getting the file ready for the grouping, and posting the code here.
 
Upvote 0
Hi Dermie_72

I recorded the script of the deletion of the columns shown below. the information that needs to be grouped now appears in column H.
I have never used Match / Countif so you will need to talk it through very basic to me

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Delete range BP:BT on selectedSheet
selectedSheet.getRange("BP:BT").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range BE:BK on selectedSheet
selectedSheet.getRange("BE:BK").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range AY:BB on selectedSheet
selectedSheet.getRange("AY:BB").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range AR:AW on selectedSheet
selectedSheet.getRange("AR:AW").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range AH:AO on selectedSheet
selectedSheet.getRange("AH:AO").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range W:AE on selectedSheet
selectedSheet.getRange("W:AE").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range E:U on selectedSheet
selectedSheet.getRange("E:U").delete(ExcelScript.DeleteShiftDirection.left);
}
 
Upvote 0
Hi Dermie_72
Sorry looking into it further I gave you the script coding. I've re-recorded the actions as a Macro and made a few adjustments

I want to add an an addition at the beginning so that destination sheet will clear contents from A2 : P1000
Please could you confirm what I need to add to the code to process this action

Also the sorting in the destination sheet can this be sorted / arranged in groups so all the rows with the matching status in column are together

Destination sheet - Column H - All possible information:
Sent
Error
Rejected
Acknowledged
Received
Accepted
Awaiting Funds
Goods Released
Hold - Documentary Check
Hold - Physical Check
Hold Customs Query
Hold - DEFRA Delay

*********

Sub PendingExport2Excel_Sort()
'
' PendingExport2Excel_Sort Macro
'

'
Range("B2:E1048576").Select
Selection.Copy
Sheets("Export to Excel Report").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("PendingExport2Excel Input").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
Range("W2:W1048576").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Export to Excel Report").Select
Range("E2").Select
ActiveSheet.Paste
Sheets("PendingExport2Excel Input").Select
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
Range("AG2:AG1048576,AH2:AH1048576").Select
Range("AH2").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Export to Excel Report").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("PendingExport2Excel Input").Select
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
Range("AQ2:AQ1048576,AR2:AR1048576").Select
Range("AR2").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Export to Excel Report").Select
Range("H2").Select
ActiveSheet.Paste
Sheets("PendingExport2Excel Input").Select
Range("AY2:AY1048576").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Export to Excel Report").Select
Range("J2").Select
ActiveSheet.Paste
Sheets("PendingExport2Excel Input").Select
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
Range("BD2:BD1048576,BE2:BE1048576").Select
Range("BE2").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Export to Excel Report").Select
Range("K2").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Range("G36").Select
Sheets("PendingExport2Excel Input").Select
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 53
Range("BM2:BP1048576").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Export to Excel Report").Select
Range("M2").Select
ActiveSheet.Paste
Range("J34").Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,375
Messages
6,171,734
Members
452,419
Latest member
mapa

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