Transfer data from sheet to another sheet with VBA

Question

I want to color the row corresponding to that cell after identifying the most data. For example, first, the highest amount of sales is identified, and next, the sales amount and the name of the person that are in one line are cut and transferred to another sheet. I am looking for a code to transfer 10% of the row's highest data to another sheet. In the first part of my code, the data is recognized correctly, but in the second part, where the data must be transferred to another sheet, the code doesn't run. Please help me.

I want 10% of the most data identified and cut from their row to another sheet.

Sub CopyHighlightedTransactions()
Dim mycell As Range
Dim myrange As Range
Dim lastrow As Long


Set myrange = Worksheets("sheet1").Range("b1:b1000")
myrange.Interior.Pattern = xlnon
For Each mycell In myrange
For i = 1 To 2
If mycell.Value = Application.WorksheetFunction.Large(myrange, i) Then
mycell.Interior.ColorIndex = 4


End If

Next
Next mycell




Dim TransIDField As Range
Dim TransIDCell As Range
Dim ATransWS As Worksheet
Dim HTransWS As Worksheet

Set ATransWS = Worksheets("sheet1")
Set TransIDField = ATransWS.Range("A2", ATransWS.Range("A2").End(xlDown))
Set TransIDField = myrange
Set HTransWS = Worksheets("sheet2")


For Each TransIDCell In TransIDField

    If TransIDCell.Interior.ColorIndex = 4 Then
    
       
        TransIDCell.Resize(1, 10).Copy Destination:= _
            HTransWS.Range("A1").Offset(HTransWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
           
    End If

Next TransIDCell

HTransWS.Columns.AutoFit

End Sub

Answer 1

You can use vba to do this but I stopped using it. It was just to frustrating and difficult to use. Instead I do it with powerquery.

1. Convert the data in your data sheet to a table
2. Follow the steps 1 here
3. In your new file that will have the top 10 choose "Get Data > From File > From Excel Workbook"
4. Rename & reorder columns as you want.
5. to get the top 10 check this video
6. click close and load.
7. You can set refresh conditions under the Data tab.

FYI data from the web