copy data from merged cell

[nasir]

I have the following sheet and need to break up the data in the cell into two columns and copy to another workbook. I also need the code to sum the data data into 0100 LOCAL FUND, 0600 SPECIAL PURPOSE REVENUE FUNDS, 0700 INTRADISTRICT FUNDS. These are the only data I want the code to copy to another workbook and insert totals, bold, line above and below.

This is how the data looks like:

Excel Workbook
	A	B	C	D	E
7	Comp Source Group				Appropriation
8	0011 * REGULAR PAY - CONT FULL TIME				2,879,113
9	0012 * REGULAR PAY - OTHER				411,762
10	0013 * ADDITIONAL GROSS PAY				124,474
11	0014 * FRINGE BENEFITS - CURR PERSONNEL				659,678
12	0015 * OVERTIME PAY				300,000
13	0099 * UNKNOWN PAYROLL POSTINGS				0
14	01 * PERSONNEL SERVICES				4,375,027
15	0020 * SUPPLIES AND MATERIALS				155,000
16	0030 * ENERGY, COMM. AND BLDG RENTALS				4,765,536
17	0031 * TELEPHONE, TELEGRAPH, TELEGRAM, ETC				97,355
18	0032 * RENTALS - LAND AND STRUCTURES				9,350,784
19	0033 * JANITORIAL SERVICES				203,618
20	0034 * SECURITY SERVICES				1,590,686
21	0035 * OCCUPANCY FIXED COSTS				1,179,726
22	0040 * OTHER SERVICES AND CHARGES				1,564,449
23	0041 * CONTRACTUAL SERVICES - OTHER				3,085,758
24	0070 * EQUIPMENT & EQUIPMENT RENTAL				262,604
25	02 * NON-PERSONNEL SERVICES				22,255,516
26	0100 * LOCAL FUND				26,630,543
27	0011 * REGULAR PAY - CONT FULL TIME				0
28	0014 * FRINGE BENEFITS - CURR PERSONNEL				0
29	01 * PERSONNEL SERVICES				0
30	0020 * SUPPLIES AND MATERIALS				0
31	0040 * OTHER SERVICES AND CHARGES				0
32	0041 * CONTRACTUAL SERVICES - OTHER				0
33	0070 * EQUIPMENT & EQUIPMENT RENTAL				0
34	02 * NON-PERSONNEL SERVICES				0
35	0150 * FEDERAL PAYMENTS				0
36	0041 * CONTRACTUAL SERVICES - OTHER				2,000,000
37	02 * NON-PERSONNEL SERVICES				2,000,000
38	0200 * FEDERAL GRANT FUND				2,000,000
39	0040 * OTHER SERVICES AND CHARGES				0
40	0041 * CONTRACTUAL SERVICES - OTHER				21,000,000
41	02 * NON-PERSONNEL SERVICES				21,000,000
42	0355 * CAPITAL FUND - FEDERAL PAYMENT				21,000,000
43	0011 * REGULAR PAY - CONT FULL TIME				743,557
44	0012 * REGULAR PAY - OTHER				135,000
45	0013 * ADDITIONAL GROSS PAY				0
46	0014 * FRINGE BENEFITS - CURR PERSONNEL				158,755
47	0015 * OVERTIME PAY				0
48	0099 * UNKNOWN PAYROLL POSTINGS				0
49	01 * PERSONNEL SERVICES				1,037,313
50	0020 * SUPPLIES AND MATERIALS				15,000
51	0030 * ENERGY, COMM. AND BLDG RENTALS				0
52	0031 * TELEPHONE, TELEGRAPH, TELEGRAM, ETC				0
53	0032 * RENTALS - LAND AND STRUCTURES				10,614
54	0040 * OTHER SERVICES AND CHARGES				1,359,010
55	0041 * CONTRACTUAL SERVICES - OTHER				5,899,110
56	0070 * EQUIPMENT & EQUIPMENT RENTAL				0
57	02 * NON-PERSONNEL SERVICES				7,283,734
58	0600 * SPECIAL PURPOSE REVENUE FUNDS				8,321,047
59	0011 * REGULAR PAY - CONT FULL TIME				10,068,040
60	0012 * REGULAR PAY - OTHER				1,001,491
61	0013 * ADDITIONAL GROSS PAY				655,000
62	0014 * FRINGE BENEFITS - CURR PERSONNEL				2,356,162
63	0015 * OVERTIME PAY				1,876,508
64	0099 * UNKNOWN PAYROLL POSTINGS				0
65	01 * PERSONNEL SERVICES				15,957,201
66	0020 * SUPPLIES AND MATERIALS				142,883
67	0030 * ENERGY, COMM. AND BLDG RENTALS				640,054
68	0031 * TELEPHONE, TELEGRAPH, TELEGRAM, ETC				307,414
69	0032 * RENTALS - LAND AND STRUCTURES				0
70	0033 * JANITORIAL SERVICES				283,149
71	0034 * SECURITY SERVICES				0
72	0035 * OCCUPANCY FIXED COSTS				0
73	0040 * OTHER SERVICES AND CHARGES				3,419,750
74	0041 * CONTRACTUAL SERVICES - OTHER				40,168,951
75	0070 * EQUIPMENT & EQUIPMENT RENTAL				1,429,756
76	02 * NON-PERSONNEL SERVICES				46,391,956
77	0700 * INTRADISTRICT FUNDS				62,349,157
Page1_1

And this is how I want the other workbook where we copied the data to look like:

Excel Workbook
	B	C	D	E
12	REGULAR PAY - CONT FULL TIME	*	0011	* * * * * * * * * * * 2,879,113
13	REGULAR PAY - OTHER	*	0012	* * * * * * * * * * * * *411,762
14	ADDITIONAL GROSS PAY	*	0013	* * * * * * * * * * * * 124,474
15	RINGE BENEFITS - CURR PERSONNEL	*	0014	* * * * * * * * * * * *659,678
16	OVERTIME PAY	*	0015	* * * * * * * * * * * *300,000
17	UNKNOWN PAYROLL POSTINGS	*	0099	* * * * * * * * * * * * * * * * * * * -
18	PERSONNEL SERVICES	*	*	* * * * *4,375,027
Table 1-YTD Exp and Fore

Thank you

 

[EducatedFool]

Try

Sub test()
    Dim cell As Range, ra As Range: Application.ScreenUpdating = False
    Set ra = Range([A8], Range("A" & Rows.Count).End(xlUp))
    For Each cell In ra.Cells
        Arr = Split(cell, " * "): cell.UnMerge
        cell.Resize(, 4) = Array(Arr(1), "*", "", "'" & Arr(0))
    Next cell
    Application.ScreenUpdating = True
End Sub

Example:

 

[nasir]

Thanks but I am getting an "Subscript out of range" error. It unmerge the rows but it did not copy the sentences in the cell to different cells.

 

[nasir]

I think the error is in

cell.Resize(, 4) = Array(Arr(), "*", "", "'" & Arr(0))

because it is highlighted.

 

[nasir]

Sorry, I meant to say

cell.Resize(, 4) = Array(Arr(1), "*", "", "'" & Arr(0))

 

[nasir]

I see where the problem is with the code. For some reason when I copied my data to this website it added * which is something I do not have in my original file
My data looks like this
0011 REGULAR PAY - CONT FULL TIME
0012 REGULAR PAY - OTHER
0013 ADDITIONAL GROSS PAY
0014 FRINGE BENEFITS - CURR PERSONNEL
0015 OVERTIME PAY
0099 UNKNOWN PAYROLL POSTINGS

There is no * between 0011 and Regular....

They are all in once cell and merged columne A - D. I need to copy the 0011 to column C of a different file called FRP and the REGULAR PAY - CONT FULL TIME to column B of FRP file.

How can I accomplish this.

Thanks

 

[nasir]

Re: copy data from merged cell (waiting for experts to help)

Please