Macro to move data from one worksheet to another in same workbook

[Mldeuser]

Is it possible to have a macro compare two wokrsheets and move the totals from one sheet to the other worksheet.

Here are the worksheets:

Excel Workbook
	B	C	D	E	F	G
6			Balance
7			as of	Misc Bill Back	Estimate	Adjusted
8		1/31/2009	1/31/2009	Accruals	KBS	Tenant Balance
9
10		ADP, Inc				-
11		Albany Molecular Research				-
12		Alder Biopharmaceuticals				-
13		Behringer USA				-
14		Brunni-Colbath				-
15		Click & Mortar Multimedia				-
16		Kaseya				-
17		Science App International				-
18		Seattle Avionics Software				-
19		Team Moose				-
20		Transas USA				-
21		Great Clips				-
22		Service Alternative				-
23		Youngzee				-
24		Pacific Vascular				-
25		Pressler Engineer				-
26		Word of His Grace				-
27		Mirabilis Medica				-
28						-
29						-
30
31
32		Per Total Aged Delinquency Balance	0.00	0.00	0.00	0.00
33
34		Tenant Rec per A/R Balance Report@01/09(A/C 1110 & 1115)				0.00
35		Prepaid Rent per A/R Balance Report @01/09(A/C 2065)				0.00
36
37						0.00
38
Prepaid Delinquent

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
7
8	Invoice Date	Category	Source		Amount	Current	1 Month	2 Months	3 Months	4 Months
9
10
11	RT1308-CB0654	ADP, Inc			Master Occupant Id: ADP, Inc-1			Day Due: 1	Delq Day:
12					4-102	Current		Last Payment:	6/29/2009	74,155
13
14	6/20/2009	UTI	Reimbursed Utilities	CH	7,864.78	0	7,864.78	0	0	0
15	6/20/2009	UTI	Reimbursed Utilities	CH	2,088.14	0	2,088.14	0	0	0
16	6/20/2009	UTI	Reimbursed Utilities	CH	7,923.44	0	7,923.44	0	0	0
17	6/20/2009	UTI	Reimbursed Utilities	CH	1,486.20	0	1,486.20	0	0	0
18	7/16/2009	UTI	Reimbursed Utilities	CH	9,394.84	9,394.84	0	0	0	0
19	7/16/2009	UTI	Reimbursed Utilities	CH	722.15	722.15	0	0	0	0
20	7/16/2009	UTI	Reimbursed Utilities	CH	8,180.08	8,180.08	0	0	0	0
21	7/16/2009	UTI	Reimbursed Utilities	CH	3,081.59	3,081.59	0	0	0	0
22
23	UTI	Reimbursed Utilities			40,741	21,379	19,363	0	0	0
24	ADP, Inc Total:				40,741	21,379	19,363	0	0	0
25
26
27	RT1308-CB0662	Albany Molecular Research, Inc			Master Occupant Id: Albany M-1			Day Due: 1	Delq Day:
28					3-101	Inactive		Last Payment:	3/5/2009	78,562
29
30	3/18/2009	OPE	Operating Expense Recove	NC	-180.27	0	0	0	0	-180.27
31	3/18/2009	OPE	Operating Expense Recove	NC	-180.27	0	0	0	0	-180.27
32	3/18/2009	OPE	Operating Expense Recove	NC	-180.27	0	0	0	0	-180.27
33	3/18/2009	OPE	Operating Expense Recove	NC	-1,401.36	0	0	0	0	-1,401.36
34	3/18/2009	OPE	Operating Expense Recove	NC	-1,401.36	0	0	0	0	-1,401.36
35	3/18/2009	OPE	Operating Expense Recove	NC	-1,401.36	0	0	0	0	-1,401.36
36	5/8/2009	OPY	Operating Exp Recov - PY	NC	-12,346.00	0	0	-12,346.00	0	0
37
38	OPE	Operating Expense Recove			-4,745	0	0	0	0	-4,745
39	OPY	Operating Exp Recov - PY			-12,346	0	0	-12,346	0	0
40	Albany Molecular Research, Inc Total:				-17,091	0	0	-12,346	0	-4,745
41
42
43	RT1308-CB0666	Kaseya, Inc			Master Occupant Id: Kaseya-1			Day Due: 1	Delq Day:
44					1-205	Current		Last Payment:	7/1/2009	9,613
45
46	7/1/2009	OPE	Operating Expense Recove	CH	76.85	76.85	0	0	0	0
47
48	OPE	Operating Expense Recove			77	77	0	0	0	0
49	Kaseya, Inc Total:				77	77	0	0	0	0
50
Aged Delinquencies

I waould like the macro to compare column "C" on the prepaid delinquent worksheet to column "A" on the Aged delinquent worksheet and where they find a match brin the total amount from column "E" on the Aged delinquent worksheet to column "D" on the Prepaid Delinquent worksheet.

Additionally if there was an entry on the Aged delinquent worksheet that it would insert a new row on the Prepaid delinquent worksheet and copy in the information from the Aged delinquent worksheet.

Any help on this would be greatly appreciated.

Thanks
Mark

 

[Bill Rockenbach]

The error was Run-time error '9'
subscript out of range
When I selected debug the following line was highlighted.

Set PrepaidNames = Sheets("Prepaid").Range("C:C")

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I suspect that the name of the Prepaid sheet is the problem. There has to be an exact match. You may have a space at the beginning or end of the name. Select the tab as if you were going to change the name. The name will be highlighted. Copy the name and pasted it in the program in place of (“Prepaid”). Be sure to put the quote marks around it.<o></o>
<o></o>
Yes, you can change the named sheets to the code name of the sheet. If you change the named sheets in the procedure to the sheet code names this should solve the above problem.<o></o>
<o></o>
Example<o></o>
<o></o>

[COLOR=black]Set PrepaidNames = Sheets("Prepaid").Range("C:C")[/COLOR]

becomes
<o></o>

[COLOR=black]Set PrepaidNames = Sheet33.Range("C:C")[/COLOR]

<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
<o></o>

 

[Mldeuser]

It works great thank you very much

 

[Mldeuser]

One last thing there is a formula in column "G" when a new row is inserted can the macro copy the formulas in column "G" to the newly inserted row.

Thanks

 

[Bill Rockenbach]

            If FoundCustomer Is Nothing Then
                Range("C5").EntireRow.Insert
                Range("C5") = CustomerName
                Range("D5") = c.Offset(0, 4)
                Range("G6").Copy Range("G5")    '<<<add this line of code
            Else

 

[Mldeuser]

Can the macro be adjusted to clear the cells prior to bringing in the new numbers. At this time it brings in the new nubers but does not remove the old ones so the reports do not tie out.

Thanks