VBA - Copying select ranges to different worksheet using multiple variables

Hi everyone.  I'm new to VBA and was wondering why I'm receiving and error message on the 2nd range I want to copy  to a new worksheet.

Every week I get a file with trade details, it's about 40+ columns with 50+ rows. I only want the top 10 by for example, "days outstanding", however, that column is not in the same column every week. I used the

".UsedRange.Columns.Find("BOOK", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column"

to locate which column the headers of my interest are in and sort by whatever. Then I used the

"Sheet1.Cells(2, cBook).Resize(11, cBook).Copy Destination:=Sheet2.Cells(5, 1)"

It works like a charm until the next range. Could someone please assist? in addition, is there a way I can use the For-Next loop to make this easier? I 'm aware I could use a variable in the destination range but how can I used a variable to go through all the expressions I declared? Thanks in advance, below is my script.

Sub Finding()

Dim tRows As Long
Dim tColumns As Long
Dim cBook As Integer
Dim cCpty As Integer
Dim cReason As Integer
Dim cCurrency As Integer
Dim cValue As Integer
Dim cUSD As Integer
Dim cDays As Integer


'Below, declaring the column headers of the Client Service data

'Book = BOOK
cBook = Sheet1.UsedRange.Columns.Find("BOOK", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column
'Cpty = CPTY_CD
cCpty = Sheet1.UsedRange.Columns.Find("CPTY_CD", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column
'Reason = REASON
cReason = Sheet1.UsedRange.Columns.Find("REASON", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column
'Currency = CCY_CD
cCurrency = Sheet1.UsedRange.Columns.Find("CCY_CD", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column
'USD EQUIV = USD_EQUIV
cUSD = Sheet1.UsedRange.Columns.Find("USD_EQUIV", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column
'Days Outstanding = DAYS_OPEN
cDays = Sheet1.UsedRange.Columns.Find("DAYS_OPEN", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column
'Effective Date = EFFECTIVE_DT
cEffect = Sheet1.UsedRange.Columns.Find("EFFECTIVE_DT", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column
'Value of Trade =  VALUE
cValue = Sheet1.UsedRange.Columns.Find(" VALUE ", LookIn:=xlValues, Lookat:=xlWhole).Cells.Column

'Determining Total Columns
tColumns = Sheet1.UsedRange.Columns.Count
'Determining Total Rows
tRows = Sheet1.UsedRange.Rows.Count

'Sort total used ranges by cUSD
Sheet1.Cells(1, 1).Resize(tRows, tColumns).Sort _
Key1:=Sheet1.Cells(1, cUSD), Order1:=xlDescending, Header:=xlYes

'Start pasting top 10 in sheet2
Sheet1.Cells(2, cBook).Resize(11, cBook).Copy Destination:=Sheet2.Cells(5, 1)
Sheet1.Cells(2, Cpty).Resize(11, Cpty).Copy Destination:=Sheet2.Cells(5, 2)
Sheet1.Cells(2, cValue).Resize(11, cValue).Copy Destination:=Sheet2.Cells(5, 3)
Sheet1.Cells(2, cUSD).Resize(11, cUSD).Copy Destination:=Sheet2.Cells(5, 4)
Sheet1.Cells(2, cEffect).Resize(11, cEffect).Copy Destination:=Sheet2.Cells(5, 5)
Sheet1.Cells(2, cDays).Resize(11, cDays).Copy Destination:=Sheet2.Cells(5, 6)

End SubStart Free Trial