Facing Problem with VBA Excel on Pulling data

Hi All,

I am working on, I have a data in different worksheet that is CHGBW, CHGBRW, REPRW, STAR, PULSE, and NYCE

I have data in all these sheet

I also have a sheet in the same workbook with the name DSMT_Procssing_LOG

I need to create a macro in DSMT_Procssing_LOG, which will help me which will pull the data one by one in the required cell on basis of the heading on the data

I want the data to get pull on basis sheet
And I have a cell that is D6 with a drop down containing all the same of the worksheet (above mentioned)

Please help

Attached is the file for reference

Thank you

Hi,

if you want to implement a list where you can use the scrollbar you have to use a listbox InSheet or switch to a UserForm, see attachment.

Regards

DSMT_Sample_Version_1.0.xlsm

I just need some clarification. If you choose CHGBW from your drop down list, you want data from the 'CHGBW' worksheet copied to the cells matching the column headers. If this is correct, which row in the 'CHGBW' worksheet do you want to copy? There are many rows.

Yes you are correct Mumps1 , I want to copy row with the same heading as mentioned in the DSMT_Procssing_LOG sheet please not that please not that the associate info data will change but not the Clint's info

Hi Nitesh. I'm still not sure what you would like to do. Do you want to copy the data you enter in the DSMT_Procssing_LOG to one of the other sheets based on what you choose in the drop down list or do you want to copy data from one of the sheets to the DSMT_Procssing_LOG? I just want to clarify which direction you want the copying done, from DSMT_Procssing_LOG to the other sheets or from the other sheets to DSMT_Procssing_LOG.

Hi Mumps1,
I want to pull the data 1 by 1 like I have a huge data in other sheet but want to work in one sheet that is DSMT_Procssing_LOG, like first I will select the my fuction that can be CHGBRW, or PULSE or anything else, then I will click on start button which will pull data data one by one like CHGBRW the heading of the data is (
RECEIVED DATE PRC SET DATE ITEMKEYS AMOUNT CARD/ACCT IREF/ARN RESULT Exception Reason Point Claim # SMT Associate Today's Date Loss Recovery Issue Log Resolve Date)
and below this there are data .

I want to pull the data one by one in the specified column in like 1st it wil pull A2 then a3 entire row and so on
and in associate data I need to update some data and save . which will save the data to the data file from which it is getting pull.

I hope this time I m able to explain ... Please let me know

Thank You
Nitesh

Hi Nitesh. I think I understand now what you would like to do. You want to copy data from your chosen sheet to the DSMT sheet one row at a time. Then you want to modify the associate data fields and save the changes back to the source sheet. Is this correct?

that correct Mumps1 you got it ... please help

Hi Nitesh. I have to go out now but I will have a look at it as soon as i can and get back to you.

Thanks You

Hi TehneXus, I am waiting for your reply

Hello Nitesh. I would need to know which ranges in the sheet DSMT_Procssing_LOG containing the associate data you want to save to the source sheet. Can I assume that they are G11:G16, J10:J17 and J21:J23? If this is correct, then you should be aware that J15:J17 (Claim Assigned, Claims Resolved, Claims Pending)do not exist in the source sheet. Please be exact as to which cells of the associate data in the sheet DSMT_Procssing_LOG you will be modifying and then saved to the source sheet. There is another issue I want to describe to you. You said that you want to pull in one row of data at a time from the source sheets so that you could work with it and then pull in the next row. This would require the macro to pause to allow you access to the data and then for the macro to pull in the next row. I don't know of any way to pause a macro. The only alternative I can think of is to prompt you for which row you want to pull in. You would enter the row number and then the macro would use that information to find the row and bring it into the sheet DSMT_Procssing_LOG. Would this work for you?

you are correct they are G11:G16, J10:J12 only

J15:J17 are formula based
Claim Assigned total number of claim or item available in the source sheet
Claims Resolved total number of data filled by associate in associate data and updated
Claims Pending total number of claim still pending to be worked


J21:J23 are also formula based

PRC its is a drop down of all the PRC which is available in the source sheet which will help me take the count of total number of items avaiable to the specific PRC
IREF/ARN : this will show the total number of IREF/ARN available to the column
Blank IREF/ARN : this will show the total number of Blank IREF/ARN (data number available in the cell)

Please note: this all will work of basis of the PRC selected and also we need to check for all PRC at a time also

Yes, that will work, infact you may also do something like after work and updating the data we can click on save button which will save the data and then we can click on start/next button which will help us get the next row of the source data

Thank You for your reply

Hi Nitesh. Please try the attached file. Choose CHGBW in cell D6 and you will be promted to enter a row number. After you make the entry, the data will be copied from sheet CHGBW. Make your changes to the associate data and click the 'Save Associate Data' button to save your changes.

Hi Mumps1,

Thank You for your reply, the file looks good, but

I think if we can do something like button "start/next" which will help me pull the data one by one instead of selecting the function and enter the the row via InputBox again n again. This will be more easier for me to work.

request you to kindly give me some code for

J15:J17 are formula based
Claim Assigned total number of claim or item available in the source sheet
Claims Resolved total number of data filled by associate in associate data and updated
Claims Pending total number of claim still pending to be worked


J21:J23 are also formula based

PRC its is a drop down of all the PRC which is available in the source sheet which will help me take the count of total number of items avaiable to the specific PRC
IREF/ARN : this will show the total number of IREF/ARN available to the column
Blank IREF/ARN : this will show the total number of Blank IREF/ARN (data number available in the cell)

Please note: this all will work of basis of the PRC selected and also we need to check for all PRC at a time also

Yes, that will work, infact you may also do something like after work and updating the data we can click on save button which will save the data and then we can click on start/next button which will help us get the next row of the source data

Thank You for your reply

Hello Nitesh. I'm glad that the code worked out for you. As I mentioned in a previous post, using a 'start/next' button is not possible because the macro would have to pause and then resume from where it left off. I'm not aware of any way of doing this. Perhaps someone else in the forum with more expertise than I may have an answer for you. As far as your additional requests are concerned (formula based questions), unfortunately my work schedule is very busy and I don't have the time to dedicate to finding a solution for you. I would like to suggest that you start a new thread to address these questions. Good luck with it.

thank You

Hi tehneXus,

Thanks for your reply, this is really helpful, but in the associate info and data info and associate ref box that is Exception Reason, Point, Claim #, Loss, Recovery, Issue Log and SMT associate resolve date and today's date we need to update data under data heading and click on save button to save the data in the main sheet

today's date will be by default today's date

Claim Assigned will count the total number of of SMT Associate for specific name
Claims Resolved will be the count of number of data updated in associate information and
Claims Pending will be the count of number of data still not updated

same way

we also have to count the the next box that is Separation box

can we also do like when we open this file the data column should be blank and after we select any function then only the data will display if available or else it should be give message that there are "NO Work Assigned"

I also need to filter the list of for PRC and iref/acct when I click of the heading
Please note iref/acct which is blank does not show in listBox

we also need to do something like when we click on start/next button it should automatically should pull the data of the next PRC

can we also do something so that we can copy the data any data (cell)

I also noticed that whenever I open the form I am not able to use any other excel file, because I have to use number of other excel file along with this...

Please check and help me on this

and once again thank you very much for your help

Regards,
Nitesh

Hi,

the things you are listing require more time than I can/want to spend here. I've changed the program Mumps1 for you to move to the next/prev record and rewritten the copy for faster processing.
You should start now actually understanding the code by reading some VBA tutorials as I won't write the entire program for you due to the lack of time.
Please find a file attached.DSMT_Sample_Version_1.1.xlsm

Regards

Thank You for your reply, sir I understand your time but though I would request your to kindly please guide/help me complete this project. I am sure it may not take lots of time for you, but sir I have been working for last 25 days by reading VBA tutorial but not able to prepare the listbox the way your did. I will be really gratified id u can prepare the listbox in this excel the way you did in the UserForm... Please please and some other question ... please ...

I'm Waiting for your reply