Macro needed to sort report

[David117]

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

 

[dermie_72]

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.

 

[David117]

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);
}

 

[David117]

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