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

 

[Mldeuser]

Is this possible??

Thanks

Mark

 

[Bill Rockenbach]

Yes it is possible, but you give to little information as to what information ("totals" is very vague) is to be moved and were it is to be moved to. Also what triggers when the information is to be moved; a button pushed, a cell value changed, the worksheet deactivated, etc?

Post back the detail

 

[Mldeuser]

The Aged delinquency report is downloaded each month and copied and pasted into this workbook.

The totals would be the total per tenant from column "E" (for example the total for ADP is $40,741) this amount would go into cell D10 on the Prepaid Deliquency worksheet.

I would be using a command button on the Prepaid delinquency worksheet.

I hope this helps

Thank you for your time on this

Mark

 

[Bill Rockenbach]

Whenever asking for solutions please give all the necessary information. Remember, we do not know anything about your operations, current reports and desired outcome except what you tell us.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
So far I think your asking to post the totals by A/R customer from the Aged Delinquencies (Aging) report to the Prepaid Delinquent (Prepaid)report by running a vba update procedure via a button on the Prepaid report. <o></o>
<o></o>
The Prepaid report already has a partial list of A/R customers. If a customer is reported on the Aging, but not on the Prepaid report, that customer is to be inserted to the Prepaid report in alphabetical order.
<o></o>
The total in column E for each customer in column A of the Aging is to be posted to column D of the matching customer in column C of the Prepaid report.
<o></o>
Questions:
Can the customer totals on the Aging be identified by the entries in column A having the word “Total:” at the end of the entry?
Are there any other entries other than the Total Customer that have the word “Total:” in them?
Is there a better way to determine the row that has the customer totals?
<o></o>
<o></o>
What information is to be added to the Prepaid report besides the customer name and total amount when a customer is missing from the Prepaid report?
<o></o>
Are both reports in the same workbook?
<o></o>
Are the matching names in both the reports always EXACTLY the same? (Most reports of this nature include the customer number. I don’t see the customer number in your sample reports.)
<o></o>
If there are any amounts in column D of the Prepaid report prior to running the update procedure, should they be erased?
<o></o>
It appears the Aging totals are rounded to the nearest dollar. Is that the way they are to be reported on the Prepaid report?

 

[Mldeuser]

So far I think your asking to post the totals by A/R customer from the Aged Delinquencies (Aging) report to the Prepaid Delinquent (Prepaid)report by running a vba update procedure via a button on the Prepaid report. This is correct
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The Prepaid report already has a partial list of A/R customers. If a customer is reported on the Aging, but not on the Prepaid report, that customer is to be inserted to the Prepaid report in alphabetical order. - This is also correct
<o></o>
The total in column E for each customer in column A of the Aging is to be posted to column D of the matching customer in column C of the Prepaid report. - You are correct
<o></o>
Questions:
Can the customer totals on the Aging be identified by the entries in column A having the word “Total:” at the end of the entry? - Yes
Are there any other entries other than the Total Customer that have the word “Total:” in them? - No
Is there a better way to determine the row that has the customer totals? - No this is the only way
<o></o>
<o></o>
What information is to be added to the Prepaid report besides the customer name and total amount when a customer is missing from the Prepaid report? - That is all that needs to be brought over if the name is missing from the prepaid report (this will be a rare occurence)
<o></o>
Are both reports in the same workbook? Yes they are
<o></o>
Are the matching names in both the reports always EXACTLY the same? (Most reports of this nature include the customer number. I don’t see the customer number in your sample reports.) - They will always be the same from month to month
<o></o>
If there are any amounts in column D of the Prepaid report prior to running the update procedure, should they be erased? - No, each month prior to running the the update procedure the workbook is saved with a new name as we need to preserve the prior monthly workbooks. So the prior information should be overwritten.
<o></o>
It appears the Aging totals are rounded to the nearest dollar. Is that the way they are to be reported on the Prepaid report? No the pennies need to show on the prepaid report. I will fix the rounding on the aged report.

I have not figured out how to use the quote option so i changed the font color for my responses.

Thank you for your help with this.

Mark

 

[Bill Rockenbach]

The names on the Aging and Prepaid reports are not EXACTLY the same in all cases. Therefore can not match names.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Albany Molecular Research, Inc <<< Aging
Albany Molecular Research <<< Prepaid
<o></o>
Kaseya, Inc <<< Aging
Kaseya <<< Prepaid
<o></o>
Please advise on the above.
<o></o>
<o></o>

If there are any amounts in column D of the Prepaid report prior to running the update procedure, should they be erased? - No, each month prior to running the the update procedure the workbook is saved with a new name as we need to preserve the prior monthly workbooks. So the prior information should be overwritten.

<o></o>

<o></o>
I think the Prepaid amounts should be erased up front in the procedure before posting data from the Aging report. There may be a name on the Prepaid report that is not on the Aging report. If that’s the case the amount will remain unless pre-erased.

 

[Mldeuser]

The names on the prepaid worksheet can be changed to match the aged worksheet.

You are right that the prior amounts on the prepaid worksheet should be removed first.

Thanks

 

[Bill Rockenbach]

I noticed teh names on teh Prepaid report are not sorted so if an Aging name is not on the Prepaid report I insert it at the top of the list of names.

If a name is in both reports but not spelled exactly the same the name in the Aging will be added to the Prepaid report.

Assume two sheets named Aging and Prepaid.

Option Explicit
Private Sub CommandButton1_Click()
    Dim c As Range
    Dim Aging As Worksheet
    Dim AgingTotals As Range
    Dim PrepaidNames As Range
    Dim CustomerName As String
    Dim FoundCustomer As Range
 
    Set Aging = Sheets("Aging")
    Set AgingTotals = Aging.Range("A1", Aging.Range("A65536").End(xlUp))
    Set PrepaidNames = Sheets("Prepaid").Range("C:C")
    For Each c In AgingTotals
        If Right(c, 6) = "Total:" Then
            CustomerName = Left(c, Len(c) - 7)
            Set FoundCustomer = PrepaidNames.Find(CustomerName, LookIn:=xlValues, lookat:=xlWhole)
            If FoundCustomer Is Nothing Then
                Range("C5").EntireRow.Insert
                Range("C5") = CustomerName
                Range("D5") = c.Offset(0, 4)
            Else
                FoundCustomer.Offset(0, 1) = c.Offset(0, 4)
            End If
        End If
    Next c
End Sub

 

[Mldeuser]

Thank you for the code.

I copied the code into module 14 and renamed the worksheets "Aging" and "Prepaid".

I then assigned the macro to a command button on the prepaid worksheet and when I ran it I received an error.

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")

I am not sure what needs to be done at this point.

I also have one other question is it possible to use the sheet numbers instead of the assigned names so if a user decides to rename the aging tab the macro will still run.

Prepaid = Sheet33
Aging = Sheet24

Thank you again for all of your work on this

Mark