Macro for extracting data

[bfadewole]

Hi guys,

I am looking for an excel macro which when I click "Run" will extract the contents of columns C through I from two worksheets "Global 1" and "Global 2" into the respective worksheets AA, FA, KS and TG based on the Marketer values in columns E. Can it also be done such that when an additional Marketer is introduced into column E on any of the Global worksheets, a new worksheet is created with the initials of the newly added marketer?

Column B in each of the worksheets where the data is extracted to should generate a counter value of 1 when there is data in column D.

Once the extraction is complete, extracted data in each of the sheets is then sorted by "Ack date with the most recent data on the bottom.

I am manually creating this currently but I know it will be a pain as the data volume increases. See sample attached.

Thanks for your help.

[sourabhg98]

Hey there,
try this macro-
Sub extract()

Please Login or Register  to view this content.

I have added my name in cell E7 of Global 1 sheet to test that new sheet is added when macro is run.

Points to be kept in mind while using macro-

1. In Cell N2 of every "Global" sheet there is the last row number for that sheet. This is a reference cell for the macro. So every time macro is hit the macro runs from the row number after the number mentioned in N2. This row number is automatically updated each time you run the macro. The only thing you need to do is when you add a new global sheet put a 2 in cell N2 of that global sheet, so that macro begins from row 2+1 = 3. In the normal course of operation it is not to be changed manually.

2. A new sheet named "format" should be placed in your workbook. This sheet acts as a format and layout for the new sheet added by the macro for a new "Marketer" recognized by it. So the "format" sheet is copied and renamed with the initials of the name of the marketer as desired.

[bfadewole]

Hi Sourabh, I really appreciate the good work. This does the job for me and I will keep in mind your points.

Thanks

[sourabhg98]

Glad to know that worked!

If your problem is solved please mark this thread as "Solved" from the "Thread Tools" at the top of the page.

Also, you can click on "Add Reputation" below my post and leave your feedback there, if that helped

Have a Great Day!!

Regards

[bfadewole]

Hi Sourabh,

I have been playing with the macro and a few items I just noticed and I would love corrections to:

1. The extracted data is not sorted by date after extraction.
2. The reference cell N2 counter in each of the Global sheets is not required and confuses the macro when I update any of the cells in the rows that were previously extracted. For example, if after the first extraction, I add or update 'Global 2'!H10:J10, it will not be captured on the next extraction. And if I change the value of 'Global 2'!N2 to 2, the whole data of Global 2 will be recreated. I would prefer N2 to always be 2 and the extraction overwrite the previous contents.

I am sorry that I might not have explained better previously.

Regards

[sourabhg98]

Hi bfadewole,
So here, to do the overwriting part macro would first delete all the data B3:I102 in all the sheets (excluding global) and then re-extract all the values from "Global" sheet from row 3 always.

Would that be fine for you?

[bfadewole]

Yes. That works fine.

Thanks

[sourabhg98]

Both issues resolved-

Please Login or Register  to view this content.

Check attached for reference.

[bfadewole]

Works like a charm. ����
Thanks

[sourabhg98]

Glad to know that worked!

If you have no further queries regarding this thread please mark this thread as "Solved" from the "Thread Tools" at the top of the page.

Also, you can click on "Add Reputation" below my post and leave your feedback there, if that helped.

Regards

[bfadewole]

Hi Sourabh,,

Thanks once again for helping out on that data extraction macro. I'll like to add a layer of complexity to the process.

I now have a second workbook with only one sheet with list of Client Name. This list is "not usually" in the same order as the Client Name on the Global sheets of the Fees and Commission workbook and there is only one sheet. This list is only for Clients that Funding Status = Yes. The objective is to have the Yes automatically populate for those Clients.

Secondly, I'll like to set a commission percentage for each Marketer that is applied into their individual sheets. For example, if the agreed commission for ANDREW AMPARO and KEN SMITH is 50% and 30% respectively, the commission cell for that Client generated by KEN SMITH should = Total Bill*0.3.

Can you please help once more with this task? let me know if you need more information.

Thanks