VBA to transfer data that satisfies a condition from one sheet to another in same workbook

[KLL2014]

Hi there! I'm very new to VBA and am trying to write a code that will do the following....

On the first sheet I have a column that lists customer IDs, a second column that lists the amount each customer has purchased, and a third column that lists the amount each customer has paid so far.

On the second sheet I need to report the customer IDs and amounts for any customer that still owes more than $1,000. I need this to work even if the data changes...especially if there are more/less customers.

This is how far I got before getting very confused....I know I need to do a loop, but just keep getting more confused. Help!!

Please Login or Register  to view this content.

[alansidman]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added the code tags for you this time. Please adhere to all our rules in the future.)

[millz]

Does the customer IDs repeat? or any new purchases/payments will be added to the existing row (meaning customer IDs only appear once)?

If the IDs only appear once, you can simply type a formula of example: =B2-C2 in cell D2

[KLL2014]

Thanks, but I need to allow for new customer IDs and purchases/payments to be added and populate dynamically. I wish I could just use a formula!!

[millz]

By populating 'dynamically', do you mean you run the macro manually after the new customer IDs are added, or do you mean the macro should run automatically after new customer IDs are entered or existing data is refreshed/entered?

How are the existing/new data entered?

[KLL2014]

No, sorry. The new data is entered manually on sheet 1. Then I'll have a macro button on sheet 2 that will "pull" any customer ID and corresponding amount owing from sheet 1 to the correct columns in sheet 2....but only if the customer owes more than $1,000. By dynamic, I mean that if I add more customers or take away customers it should "read" those lines the same. Does that make sense?

[millz]

Try running this macro with your button on sheet2:

Please Login or Register  to view this content.

[KLL2014]

Thank you for your response. It's not working, but you've given me a place to work from at least. I think I should be able to work through it from here. Thanks again!

[millz]

It's not working

Well, it's working when I tested on dummy data. If there are more problems, post a sample of your workbook.

[KLL2014]

Here you go!

[KLL2014]

I'm getting Runtime Error '9':
Subscript out of range

[millz]

Try this:

Please Login or Register  to view this content.