Excel/Extracting data vba
In sheet1, I have three columns. Column "A" contains a name. Columns "B&C" contain numbers. I cannot sort the columns because they are fed to excel in a certain format.
I need a VBA script that will identify the numbers in Column "B" between 70 and 75 and place them in descending order along with the corresponding names in Sheet2. If the numbers in Column "B" are the same the ranking should look for values in column "C".
The end result should be a list of names with values between 70 and 75 with a secondary descending ranking for Column "C".
I believe the following macro does what you describe. I assumed that neither of the two sheets had a heading row, but you can add space for a header row on Sheet1 by initializing the iRow loop at 2 instead of 1, and on Sheet2 by initializing dRow = 2 instead of 1.
Here's the code:
Dim iRow As Long 'Row index on source worksheet
Dim dRow As Long 'Row index on destination worksheet
Dim FromSh As Worksheet 'Source worksheet
Dim DestSh As Worksheet 'Destination worksheet
Set FromSh = Worksheets("Sheet1")
Set DestSh = Worksheets("Sheet2")
dRow = 1
'copy data to destination worksheet
For iRow = 1 To .Range("B65536").End(xlUp).Row
If .Cells(iRow, "B") > 70 And .Cells(iRow, "B") < 75 Then
.Range(.Cells(iRow, "A"), .Cells(iRow, "C")).Copy _
dRow = dRow + 1
'sort the destination worksheet data using column B as the primary
'sort key and column C as the secondary
.Range("A1", .Cells(dRow - 1, "C")).Sort Key1:=.Range("B1"), key2:=.Range("C1"), _
I hope you find this helpful.