• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

code for copying and pasting based on criteria

V

Veeru106

Member
"I am working on one file, where I need to add fund id in multiple sheets in a workbook. In 3 sheets it is quite easy as I just need to add it in the below of existing data but in one sheet (AGG ""US"") I need to add in group of that id. Like I have groups name as ""USI,TYU,YTR"" and ids are mentioned in these groups.so if my id belongs to USI then i need to add in tab ""AGG US"" under USI group only and so on. So new fund id identified belongs to USI will add in AGG ""US"" tab in USI group and other 3 tabs as well under existing data.
Can any one suggest a code so that i can automate it. Sample file attached"

Thanks as always......
 

Attachments

  • Book1.xlsx
    12.3 KB · Views: 6
Hi ,

I think some more explanation is required.

1. What does the following mean ?
So new fund id identified belongs to USI will add in AGG ""US"" tab in USI group and other 3 tabs as well under existing data.
Click to expand...
2. How and where will you add new fund IDs ?

3. Are these the only worksheet tabs and fund ID groups that will be used , or are more likely to be added ?

More questions may come up as we go along.

Narayan
 
Thanks Narayank for looking into this.
Here are the answers:-
    • So if we go to Sheet 4, we have new id "555" which belongs to region "BHT". That means every time I have new id , it will belong to particular region always (AGG "US") has all region mentioned in it.

    • New id identified will add back to all tabs except Sheet4 tab, now as mentioned above every id will belong to particular region , hence in first tab (AGG "US") , it should add in that particular region (like above id 555 should add in BHT region on first tab), where in other tabs it will add below existing data (no Criteria required for others tab).

    • Yes and No, Yes we will be adding new ids (Col. 2 in AGG "US Tab) every time and No , we will not add any more group or region in future. (Col. 1 in AGG "US Tab)


    Please let me know, if more info required.

    Thanks


 
Hi ,

Let me summarize :

1. The group and ID will be entered in Sheet4 , in cells B2 and C2.

The workbook at present contains BHT in cell B2 and 555 in cell C2.

2. This will now be entered in sheet AGG "APA" in cell A8.

3. This will also be entered in sheet AGG "EU" in cell A9.

4. This will also be entered in sheet AGG "NA" in cell A10.

5. In sheet AGG"US" , a new row will be inserted in row 22 , and cell A22 will have BHT and cell B22 will have 555.

Is all of this correct ?

Narayan
 
Hi Narayan,

Yes for point 1, whenever we have any new id , we will mentioned them in cell B2 and C2 respectively
and all yes for other points...

Thanks
 
Thanks Narayan,
you have created Macro in sheet4 in VBA window. I have copy it and pasted in module 1 and try to run it but I am not able to assign this macro to command button as while assigning I am not able to see any macro in window. Please suggest how to assign it.
 
Hi ,

The code is within an event procedure called Worksheet_Change ; this cannot be assigned to a button. It will run automatically when ever you change B2 or C2.

If you wanted that the code should run when you click a button , this should have been mentioned in the initial post.

Narayan
 
Sorry Narayan for not mentioning this in first time but current situation will be even better...I didn't know this something in VBA...Thank you very much for letting me know about this....
Last thing...do we need to change B2 and C2 in one go , because if I change B2 first , which is group and before i changed C2, code runs and add previous code in current group..
 
Hi ,

The code is checking for whether both the cells are populated with fresh data.

When ever you wish to add either a new fund ID , first clear both the cells , and then enter the group and ID in any order ; only when both the cells are populated will the entry be transferred to the other tabs.

If you wish to retain the existing group in B2 , clear only C2 and make a fresh entry there.

Narayan
 
Hi Narayan,
Need little modification to the above code.

I have attached new workbook , where id column has been changed to reflect my current situation

I have inserted name column in some sheets and accordingly we need some modification in code as well.
 
Hi ,

The tab named AGG "APA" does not have the Name field ; the other tabs all have the Name field.

So when adding an ID , or a Group and ID , should the Name field be left blank ?

Narayan
 
Sorry Narayan for coming back on this...while applying same code on my actual sheet I am facing some issue. I have changed the sheets names to
Code: 
Sheetnames = Array("NCF_Data", "FundName Rollup Mapping", "Prod Category Lookup", "AuM_Data", "Retail Flash NCF by Month")

So not sure whether this is correct. Can you please take a look
 
You must log in or register to reply here.
Back
Top