Identify and select range, then sort, then transpose copy to another sheet then use VLOOKUP

[mdeshazo]

Here's the sequence of events and below is the spreadsheet and my poor attempt at code thus far.

I'd really like to have the code execute the macro code directly after the user pastes the information into the workbook.

I will have the user always cut-n-paste information into C35, similar to below, in columns C:J, the rows are variable (could be more or less).

After identifying the range, I then need to sort so that it sorts ascending in column D (to move the budget marked with a "P" to the top), then sort the remaining budgets in ascending order based on the budget number.

After doing this, need to name a range beginning in C37 so that it can then be used for several purposes throughout the rest of the workbook (specifically in a series of VLOOKUP formulas.)

Ok that's my first attempt at explaining this. I can't even get past the identifying the the range I want, much less the next few steps. Note that I have been scouring the board for the last 4 days and have pieced together things that have not worked for me, so I know I'm missing something fundamental here but it escapes me.

Thank you for any assistance,
Michael D.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
34		IF there are sub-budgets,
35		PASTE GRANT TRACKER SCREEN HERE >>>>	Budget		Award	Revenue	Expenditures	Encumbrance	Balance	Receipt
36			610346	S	$373,502.00	($373,502.00)	$373,502.00	$0.00	$0.00	$0.00
37	1	Sort Column D as Ascending (moves P to top of list), then sort budget#	610362	S	$58,409.00	($58,409.00)	$58,409.00	$0.00	$0.00	$0.00
38	2	create a list of budgets to transpose to F&A Checklist!$AE3	610844	S	$29,646.00	($29,646.00)	$29,646.00	$0.00	$0.00	$0.00
39	3	also copy/paste Total Expenditure data to F&A Checklist!$AE7	611297	S	$47,797.00	($47,797.00)	$47,797.00	$0.00	$0.00	$0.00
40	4	also copy/paste Total Expenditure data to Info Display!$H21	611886	S	$74,511.00	($74,511.00)	$74,511.00	$0.00	$0.00	$0.00
41	5	Copy Exp to FA Checklist	612116	S	$18,865.00	($18,865.00)	$18,865.00	$0.00	$0.00	$0.00
42			612291	S	$4,548.00	($4,548.00)	$4,548.00	$0.00	$0.00	$0.00
43			612354	S	$203,464.00	($203,464.00)	$203,414.71	$0.00	$49.29	$0.00
44			612430	S	$21,881.00	($21,881.00)	$21,881.00	$0.00	$0.00	$0.00
45			612550	S	$19,314.00	($19,314.00)	$19,314.00	$0.00	$0.00	$0.00
46			612706	S	$36,315.00	($36,315.00)	$36,315.00	$0.00	$0.00	$0.00
47		FirstBudgetRow = C36	612710	S	$2,496.00	($2,496.00)	$2,496.00	$0.00	$0.00	$0.00
48			612713	S	$34,644.00	($34,644.00)	$34,644.00	$0.00	$0.00	$0.00
49			612715	S	$22,176.00	($22,176.00)	$22,176.00	$0.00	$0.00	$0.00
50			612740	S	$307,348.00	($307,348.00)	$307,348.06	$0.00	($0.06)	$0.00
51			612741	S	$169,361.00	($169,361.00)	$169,361.00	$0.00	$0.00	$0.00
52			612742	S	$483,099.00	($483,099.00)	$456,238.94	$27,228.26	($368.20)	$0.00
53			613879	S	$63,833.00	($63,833.00)	$63,833.00	$0.00	$0.00	$0.00
54			613880	S	$9,951.00	($9,951.00)	$9,951.00	$0.00	$0.00	$0.00
55			614015	S	$9,831.00	($9,831.00)	$9,831.00	$0.00	$0.00	$0.00
56			614581	S	$31,455.00	($31,455.00)	$31,455.00	$0.00	$0.00	$0.00
57			614714	S	$215,879.00	($215,879.00)	$215,879.00	$0.00	$0.00	$0.00
58			629423	S	$150,781.00	($150,781.00)	$150,781.00	$0.00	$0.00	$0.00
59			629442	S	$290,770.00	($290,770.00)	$290,770.00	$0.00	$0.00	$0.00
60			629452	S	$227,761.00	($227,761.00)	$227,760.63	$0.00	$0.37	$0.00
61			629453	S	$263,384.00	($263,384.00)	$263,384.00	$0.00	$0.00	$0.00
62			629465	S	$133,638.00	($133,638.00)	$133,638.00	$0.00	$0.00	$0.00
63			629616	S	$422,390.00	($422,390.00)	$503,246.23	$0.00	($80,856.23)	$0.00
64			629617	S	$1,767,491.00	($1,767,491.00)	$1,809,417.40	$0.00	($41,926.40)	$0.00
65			629618	S	$803,311.00	($803,311.00)	$925,356.77	$0.00	($122,045.77)	$0.00
66			629619	S	$167,892.00	($167,892.00)	$167,892.00	$0.00	$0.00	$0.00
67			629620	S	$150,000.00	($150,000.00)	$149,964.22	$1.00	$34.78	$0.00
68			629621	S	$169,920.00	($169,920.00)	$169,924.53	$0.00	($4.53)	$0.00
69			629622	S	$66,962.00	($66,962.00)	$66,962.00	$0.00	$0.00	$0.00
70			629623	S	$242,219.00	($242,219.00)	$242,219.00	$0.00	$0.00	$0.00
71			629624	S	$210,079.00	($210,079.00)	$210,079.00	$0.00	$0.00	$0.00
72			629625	S	$263,462.00	($263,462.00)	$263,462.00	$0.00	$0.00	$0.00
73			629626	S	$360,308.00	($360,308.00)	$360,308.00	$0.00	$0.00	$0.00
74			629627	S	$400,367.00	($400,367.00)	$400,367.00	$0.00	$0.00	$0.00
75			629628	S	$271,785.00	($271,785.00)	$271,785.00	$0.00	$0.00	$0.00
76			629629	S	$186,488.00	($186,488.00)	$186,488.00	$0.00	$0.00	$0.00
77			629630	S	$204,140.00	($204,140.00)	$204,140.00	$0.00	$0.00	$0.00
78			629631	S	$241,424.00	($241,424.00)	$241,424.00	$0.00	$0.00	$0.00
79			629639	S	$239,000.00	($239,000.00)	$279,081.08	$0.00	($40,081.08)	$0.00
80			650451	S	$470.00	($470.00)	$490.75	$0.00	($20.75)	$470.00
81			650452	S	$66,329.32	($66,329.32)	$68,248.43	$0.00	($1,919.11)	$4,285.58
82			650453	S	$17,470.55	($17,470.55)	$19,431.42	$0.00	($1,960.87)	$51.06
83		629416	629416	P	$422,681.00	($422,681.00)	$147,354.19	$74,986.95	$200,339.86	$0.00
DATA INPUT ONLY

The code (attached to the worksheet):
Sub DefineBudgetList()
Dim LR1 As Integer
Dim SortRange1 As Range
Dim SR1 As Integer
Dim BudgetList As Range

' identify the last row of the copied range
LR1 = Range("C65536").End(xlUp).Row
Set SortRange1 = Range("C35:C" & LR1).Resize(, 8).Select
'Set SortRange1 = Range(Cells(35, "C"), Cells(Cells(Rows.Count, "C").End(xlUp).Row, "C"))
With Sheets("DATA INPUT ONLY")
.SortFields.Clear
.SortFields. _
Add Key:=Range("D36:D" & LR1), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SortFields. _
Add Key:=Range("C36:C" & LR1), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.SetRange Range("C35:J" & LR1)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub