expert help needed

[nasir]

I have 8 tabs in file 1. I have a code which opens another file, file2. I need a vba that searches the value in cell A1 of each tab in file 1 from file2 column A:A. This file2 has only one tab.

If the values are found, I need the vba to copy the data for each value

For e.x. If the value in cell A1 in tab1 is 100, the vba will search that value from column A:A in file2. If it finds, the vba wil copy the rows specific to 100. You can identify the data specific to 100 if another value that is not 100 starts.

Excel Workbook
	A	B	C	D
8	0100	*	*	*
9	Object Category	GAAP Category Title	Comptroller Source Group	Comptroller Source Group Title
10	01	PERSONNEL SERVICES	0011	REGULAR PAY - CONT FULL TIME
11			0012	REGULAR PAY - OTHER
12			0013	ADDITIONAL GROSS PAY
13			0014	FRINGE BENEFITS - CURR PERSONNEL
14			0015	OVERTIME PAY
15			0099	UNKNOWN PAYROLL POSTINGS
16		PERSONNEL SERVICES
17	02	NON-PERSONNEL SERVICES	0020	SUPPLIES AND MATERIALS
18			0030	ENERGY, COMM. AND BLDG RENTALS
19			0031	TELEPHONE, TELEGRAPH, TELEGRAM, ETC
20			0032	RENTALS - LAND AND STRUCTURES
21			0033	JANITORIAL SERVICES
22			0034	SECURITY SERVICES
23			0035	OCCUPANCY FIXED COSTS
24			0040	OTHER SERVICES AND CHARGES
25			0041	CONTRACTUAL SERVICES - OTHER
26			0070	EQUIPMENT & EQUIPMENT RENTAL
27		NON-PERSONNEL SERVICES
28	0100
29	1359	*	*	*
30	Object Category	GAAP Category Title	Comptroller Source Group	Comptroller Source Group Title
31	01	PERSONNEL SERVICES	0011	REGULAR PAY - CONT FULL TIME
32			0012	REGULAR PAY - OTHER
33			0013	ADDITIONAL GROSS PAY
34			0014	FRINGE BENEFITS - CURR PERSONNEL
35			0015	OVERTIME PAY
36			0099	UNKNOWN PAYROLL POSTINGS
37		PERSONNEL SERVICES
38	02	NON-PERSONNEL SERVICES	0020	SUPPLIES AND MATERIALS
39			0031	TELEPHONE, TELEGRAPH, TELEGRAM, ETC
40			0032	RENTALS - LAND AND STRUCTURES
41			0035	OCCUPANCY FIXED COSTS
42			0040	OTHER SERVICES AND CHARGES
43			0041	CONTRACTUAL SERVICES - OTHER
44			0070	EQUIPMENT & EQUIPMENT RENTAL
45		NON-PERSONNEL SERVICES
46	1359
47	1365	*	*	*
48	Object Category	GAAP Category Title	Comptroller Source Group	Comptroller Source Group Title
49	01	PERSONNEL SERVICES	0011	REGULAR PAY - CONT FULL TIME
50			0012	REGULAR PAY - OTHER
51			0013	ADDITIONAL GROSS PAY
52			0014	FRINGE BENEFITS - CURR PERSONNEL
53			0015	OVERTIME PAY
54			0099	UNKNOWN PAYROLL POSTINGS
55		PERSONNEL SERVICES
56	02	NON-PERSONNEL SERVICES	0020	SUPPLIES AND MATERIALS
57			0030	ENERGY, COMM. AND BLDG RENTALS
58			0031	TELEPHONE, TELEGRAPH, TELEGRAM, ETC
59			0033	JANITORIAL SERVICES
60			0034	SECURITY SERVICES
61			0040	OTHER SERVICES AND CHARGES
62			0041	CONTRACTUAL SERVICES - OTHER
63			0070	EQUIPMENT & EQUIPMENT RENTAL
64		NON-PERSONNEL SERVICES
65	1365
Page1_1

Thanks

 

[ravishankar]

HI
try the following codes.

Sub Nasir()
Dim a As Long, g As Long
Sheets.Add.Name = "Summary"
    For a = 1 To Sheets.Count
        If Worksheets(a).Name <> "summary" Then
        Sheets("summary").Cells(a + 1, 1) = Worksheets(a).Name
        Sheets("summary").Cells(a + 1, 2) = Worksheets(a).Cells(1, 1)
            For g = 1 To 6
            Sheets("summary").Cells(1, 3) = "=vlookup(B" & a & ",'C:\mydocumnents\[Book2.xls]Sheet1!A$5:$G$13," & g & ",false)"
            Sheets("summary").Cells(a + 1, 3) = Sheets("summary").Cells(1, 3)
            Next g
        End If
    Next a
MsgBox "collating is complete."
End Sub

change the filepath, filename and sheet name of book2 in the codes above. No need to open book2. it pulls data from closed book.On running macro, it adds a sheet called summary, lists tab names in col A, Cell value of A1 in col b and corresponding row data in subsequent columns.
Ravi

 

[nasir]

Thanks Ravi but it did not work. I tweak the code but getting error messages:

Sub Nasir()
Dim a As Long, g As Long
Sheets.add.Name = "Summary"
For a = 1 To Sheets.Count
If Worksheets(a).Name <> "summary" Then
Sheets("summary").Cells(a + 1, 1) = Worksheets(a).Name
Sheets("summary").Cells(a + 1, 2) = Worksheets(a).Cells(1, 1)
For g = 1 To 6
Sheets("summary").Cells(1, 3) = "=vlookup(B" & a & ",'C:\[FRP1013.xlsm]Table 1-YTD Exp and Fore!A$5:$G$13," & g & ",false)"

Sheets("summary").Cells(a + 1, 3) = Sheets("summary").Cells(1, 3)
Next g
End If
Next a
MsgBox "collating is complete."
End Sub


FRP1013 is the file I want to show the results in. And that is where I entered the code. The code need to look at at the tab numbers, 0100, 1450, 1459,1460 to name a few and also look at the D12 and want to the code to code down that column. D13, D14....The code need to look for these numbers in file 09FRP, next month the file will change to 10FRP, 11 FRP, 12 FRP and 01FRP, 02FRP etc. Once these numbers are found in 09FRP the code should retunn the value in column e and put it in cell E12 and the code will go down the list.

Example,
If tab says 0100 and cell D12 is 0011 the code will look these two values in 09FRP and return value in E10.
0100
Object Category GAAP Category Title Comptroller Source Group Comptroller Source Group Title Appropriation Current AY Expenditure (Less I-D Adv) Intra- District Advances Encumbrance Pre Encumbrance Current Available Balance Prior AY Expenditure Prior AY Obligation FY Available Balance Percent Available
01 PERSONNEL SERVICES 0011 REGULAR PAY - CONT FULL TIME 2,879,113 1,544,694 0 0 0 1,334,419 0 0 1,334,419 46.3%
0012 REGULAR PAY - OTHER 96,738 1,061,947 0 0 0 -965,210 0 0 -965,210 -997.8%
0013 ADDITIONAL GROSS PAY 0 106,467 0 0 0 -106,467 0 0 -106,467 0.0%
0014 FRINGE BENEFITS - CURR PERSONNEL 544,563 494,668 0 0 0 49,895 0 0 49,895 9.2%
0015 OVERTIME PAY 300,000 83,043 0 0 0 216,957 0 0 216,957 72.3%
0099 UNKNOWN PAYROLL POSTINGS 0 0 0 0 0 0 0 0 0 0.0%
PERSONNEL SERVICES 3,820,414 3,290,820 0 0 0 529,594 0 0 529,594 13.9%
02 NON-PERSONNEL SERVICES 0020 SUPPLIES AND MATERIALS 155,000 85,467 22,227 46,521 0 785 0 0 785 0.5%
0030 ENERGY, COMM. AND BLDG RENTALS 4,765,536 5,671,744 1,085,535 0 0 -1,991,743 0 0 -1,991,743 -41.8%
0031 TELEPHONE, TELEGRAPH, TELEGRAM, ETC 97,355 111,026 -13,671 0 0 0 0 0 0 0.0%
0032 RENTALS - LAND AND STRUCTURES 9,350,784 5,863,092 3,487,692 0 0 0 0 0 0 0.0%
0033 JANITORIAL SERVICES 203,618 142,176 61,442 0 0 0 18,864 -18,864 0 0.0%
0034 SECURITY SERVICES 1,590,686 530,096 1,060,590 0 0 0 0 0 0 0.0%
0035 OCCUPANCY FIXED COSTS 1,179,726 617,718 562,007 0 0 0 0 0 0 0.0%
0040 OTHER SERVICES AND CHARGES 1,981,629 994,643 465,574 136,227 0 385,184 -18,864 18,864 385,184 19.4%
0041 CONTRACTUAL SERVICES - OTHER 4,595,796 392,932 631,440 740,482 28,736 2,802,206 0 0 2,802,206 61.0%
0070 EQUIPMENT & EQUIPMENT RENTAL 240,000 25,229 231,640 5,735 0 -22,604 0 0 -22,604 -9.4%
NON-PERSONNEL SERVICES 24,160,129 14,434,123 7,594,476 928,966 28,736 1,173,829 0 0 1,173,829 4.9%
0100 27,980,543 17,724,943 7,594,476 928,966 28,736 1,703,423 0 0 1,703,423 6.1%
1359
Object Category GAAP Category Title Comptroller Source Group Comptroller Source Group Title Appropriation Current AY Expenditure (Less I-D Adv) Intra- District Advances Encumbrance Pre Encumbrance Current Available Balance Prior AY Expenditure Prior AY Obligation FY Available Balance Percent Available
01 PERSONNEL SERVICES 0011 REGULAR PAY - CONT FULL TIME 4,259,935 3,168,042 0 0 0 1,102,055 0 -10,161 1,112,216 25.9%
0012 REGULAR PAY - OTHER 337,251 743,141 0 0 0 -405,890 0 0 -405,890 -120.4%
0013 ADDITIONAL GROSS PAY 335,000 555,948 0 0 0 -220,948 0 0 -220,948 -66.0%
0014 FRINGE BENEFITS - CURR PERSONNEL 970,357 782,140 0 0 0 188,217 0 0 188,217 19.4%
0015 OVERTIME PAY 515,000 304,521 0 0 0 210,479 0 0 210,479 40.9%
0099 UNKNOWN PAYROLL POSTINGS 0 0 0 0 0 0 0 0 0 0.0%
PERSONNEL SERVICES 6,417,543 5,553,791 0 0 0 873,913 0 -10,161 884,075 13.6%
02 NON-PERSONNEL SERVICES 0020 SUPPLIES AND MATERIALS 61,500 19,213 0 23,002 0 18,835 0 450 18,385 30.6%
0031 TELEPHONE, TELEGRAPH, TELEGRAM, ETC 0 0 0 0 0 0 0 0 0 0.0%
0032 RENTALS - LAND AND STRUCTURES 0 0 0 0 0 0 0 0 0 0.0%
0035 OCCUPANCY FIXED COSTS 0 0 0 0 0 0 0 0 0 0.0%
0040 OTHER SERVICES AND CHARGES 745,000 781,110 -140,757 98,648 0 -4,162 0 10,161 -14,323 -0.6%
0041 CONTRACTUAL SERVICES - OTHER 7,930,187 2,798,499 443,663 3,118,025 600,922 1,197,651 -235,486 6,912 1,426,225 15.1%
0070 EQUIPMENT & EQUIPMENT RENTAL 130,000 54,883 0 5,546 0 69,571 0 0 69,571 53.5%
NON-PERSONNEL SERVICES 8,866,687 3,653,705 302,906 3,245,222 600,922 1,281,895 -235,486 17,523 1,499,858 14.5%
1359 15,284,230 9,207,495 302,906 3,245,222 600,922 2,155,809 -235,486 7,362 2,383,932 14.1%