Excel VBA - Return Specific Data from Master Sheet Based on Date Search + Duplicate Certain Items

[LookingForHelp]

I am a relative beginner to VBA and am a bit over my head on this problem. Not sure anyone will be able to help with this, but I suppose typing my thoughts out might help me to wrap my own brain around exactly what I need to do in any case.

The Problem

I have a large worksheet of data in one master sheet (titled "CPO"). I am looking to create a macro to return specific data from that sheet to another (titled "Search") based on a matching date search. The date is located in cell 'B2' of Search. I would like to paste these values in the Search sheet beginning in row 5.

Date values in the master sheet are located in column 'J'. For each entry matching the query date in Column J, I would like to return the data from column 'A' in CPO to column 'A' in Search; from column 'B' in CPO to column 'B' in search; from column 'F' in CPO to column 'C' in Search; from column 'H' in CPO to column 'D' in Search; and from column 'L' in CPO to column 'E' in Search.

A complicating factor is that I need to duplicate this such that each returned row of data appears twice, once with the final pull from column 'L' in CPO appearing in column 'E' in Search, and once with the same number from column 'L' in CPO appearing in column 'F' in search (debits and credits).

Finally, for returned values labeled as "Principal" (located in column 'H' of CPO, to be returned to column 'D' of search), I need to duplicate this once more. The result would be two debits and two credits for each principal payment, and one debt and one credit for each interest payment. Debit would be in column 'E' of Search, and credits would be in column 'F' of Search.



I hope I've described that clearly enough. I realize it is a complicated problem (especially for me), and is something that might be better suited for a different program--unfortunately I need to do this in Excel.

Thanks in advance to anyone who takes the time to read all of that.



I've tried looking around for examples of similar problems online but haven't found something that quite does the trick as of yet.

 

[Aniruddah Alam]

A complicating factor is that I need to duplicate this such that each returned row of data appears twice, once with the final pull from column 'L' in CPO appearing in column 'E' in Search, and once with the same number from column 'L' in CPO appearing in column 'F' in search (debits and credits).

Finally, for returned values labeled as "Principal" (located in column 'H' of CPO, to be returned to column 'D' of search), I need to duplicate this once more. The result would be two debits and two credits for each principal payment, and one debt and one credit for each interest payment. Debit would be in column 'E' of Search, and credits would be in column 'F' of Search.

Dear LookingForHelp!

Thanks for posting in the Exceldemy Forum. By looking at your problem, I believe we can provide a solution. But before that, I just wanted to clarify something. I understood the first part of your post, but I'm a little unsure about the latter part where you mentioned duplicating the search result. Could you provide a sample Excel file with some sample data to help me better understand what you're trying to achieve in both the CPO and SEARCH sheets? Once I have a better idea of what you need, I'm hopeful that I'll be able to provide some solutions that will work for you.

Regards
Aniruddah
Team Exceldemy