May 07 2021 11:23 PM - edited May 10 2021 02:45 AM
Hello i want to copy-paste data from one workbook in to another work book based on specific custom cells range and then paste in to another sheet to push to database and for internal record. The problem is data is in different formats for example in workbook 1 Words such as "Decrease" to be pasted as just "D" in target workbook, and "Invalid Card" to be pasted as Digit "5" and Word "Other" as 6
For example the data in above sheet is coming via a web form filled by a shop owned and then want to save it via a macro import in the workbook below without manual selecting each cell
Please help any help is appreciated.
Any help will be highly appreciated, thanks in advance.
@Faraz Shaikhany ideas ? please
May 14 2021 10:20 PM
May 17 2021 01:31 AM
@Yea_SoHello Below is the attached workbook, so i want to import data FROM sheet2 in to Sheet1 using some recorded Macro to automate the manual copy-paste and typing abbreviations such as "D" for Decrease and "1" for True and "0" for False. Thanks in advance for any help or ideas or suggestions given in this regard .
May 17 2021 03:02 AM - edited May 17 2021 03:05 AM
Hi @nidospartan
Here is the edits I did to your workbook:
Your sample data:
Converted data using excel formulas:
Data moved Destination Form using UNIQUE Funtion:
All you have to do is copy and paste to the Source Data Sheet, and it will automatically convert the data based on the Look up Sheet which you can edit/add they're dynamic tables so the range will autoadjust when you add items:
Let me know if you have any questions
May 17 2021 05:18 AM
@Yea_So Thank you big time for the help, but issue is the data in source sheet comes from a web form and then it is copy pasted for few columns every time a form arrives. Then manually typed based on values in Source Sheet and converted to Match the validation rules in Destination sheet to further push in to sql db. So i want to set rules as Validation to create a sort of empty template out of destination sheet.
So that every time a form arrives with a click of macro import data gets pasted on empty cells based on set validation rules and then pushed to next system and saved also as a copy for the record in another excel sheet.
I guess the best is to use some kind of RPA flow from inside excel destination sheet to copy only selected column fields and then translate them based on validation rules so that no incorrect values can be inserted in to destination sheet then eventually in to database server.
Last thing, how can i replace "N/A" with 0 ? or empty ? to insert as "null" value in db ?
May 17 2021 05:24 AM
May 17 2021 11:52 PM
May 17 2021 11:56 PM
May 18 2021 02:29 PM
@nidospartan if your problem is:
"The problem i am facing is when data comes from Web form in an excel sheet"
from what you said above, the Web form is an excel sheet. excel power query can point to the web address of the excel sheet Web form and display it into your local excel without copying and pasting, and once you have the power query set up to bring the data in your excel you can either use macro on your excel or other data manipulation to it.