With code Get Selected Data from one sheet to another

Hello experts,
Refering the code shared by sintek, I am trying to get data from one sheet to a newly created sheet. I have been able to get selected data of all the columns but they are not in the required order. Maybe I missed something which selects a column from one sheet and posts in the wanted column. Also, I need to fill data with values in a few columns as mentioned in the expected sheet. Once again, I need your expertise to get me the expected result and most important of all I request to write the comments. Removed the links from the workbook and attached once again with the expected result.
Thank you in advance.

Hello RAJESH SHAH
Let's see if I understand: what we see on the 'Step2 Expected' sheet should go on the 'Step2' sheet instead of what Button 1 is creating on that sheet: Is that so?...

Yes. The code will first delete the step2 sheet and then recreates it with the result. Please refer the replaced attachment. The Step2 Expected sheet is just for referring how the result will be.

Ok. Try with:

beyond excel. Thanks man. It is what I expected except for 2 things. The last column is a formula and I want 18 instead of 18%. And how do I remove the table design and get the data normal. As the next step will select the table from this sheet, I hope the table doesn't affect it.

Please Login or Register  to view this content.

Thank you sintek. It is perfect man. I still have many lines to understand to try and write the code like you do. When exactly is colarray used to Store the required columns of source sheet and why have you not used that in this code.?

This just stores the column numbers in a variable...To be used later

Please Login or Register  to view this content.

I just bypassed that...No specific reason..either or...

Please Login or Register  to view this content.

I still have many lines to understand

Only thing above which could be confusing is the Evaluate Functions...i am merely passing the values immediately by evaluating the formulas...

Ok. Array(1, 8, 16, 9, 10, 11, 12, 14, 1, 1, 1, 8, 13)). I am beginning to understand a bit now. Number order as per display in destination sheet not necessarily ascending order.
I have edited this one line but please edit again to get the round to 2 decimals.

Please Login or Register  to view this content.

Number order as per display in destination sheet not necessarily ascending order.

Just merely extracting those columns from Q4 sheet to populate order of Step 1 sheet...You will notice some dummy columns used...
2 decimals is displayed...

Originally Posted by sintek

This just stores the column numbers in a variable...To be used later

Please Login or Register  to view this content.

I just bypassed that...No specific reason..either or...

Please Login or Register  to view this content.

Only thing above which could be confusing is the Evaluate Functions...i am merely passing the values immediately by evaluating the formulas...

Yes. I am comparing that kind of formulas with your evaluate function in different modules, but if a new function is required I will let you know. Like the one to fill the column with a particular value was new and in this case how you wrote that line which will be useful in future.

Please Login or Register  to view this content.

Glad to have helped...tx for rep +
Happy coding...

Originally Posted by RAJESH SHAH

beyond excel. Thanks man. It is what I expected except for 2 things. The last column is a formula and I want 18 instead of 18%. And how do I remove the table design and get the data normal. As the next step will select the table from this sheet, I hope the table doesn't affect it.

That's very simple:

a) Instead of:

Please Login or Register  to view this content.

uses:

Please Login or Register  to view this content.

b) Remove the line:

Please Login or Register  to view this content.

c) And add as last line:

Please Login or Register  to view this content.

Thanks for the update beyond excel.

Sintek, Can you please explain this line.? I am using this type of lines regularly and need to understand. ? What is T in (VLOOKUP(T(I...?

Please Login or Register  to view this content.

I am trying to edit it with the below formula but I am not getting the expected result.
=VLOOKUP(B2,'State Code'!$C$3:$H$39,6,0)

Please Login or Register  to view this content.

This is the image of the sheet where I got all the results correct except column P. The formula is visible in the display.
Attachment 827420

Attachment is invalid...

What is T

Refers Text

Formula:

Please Login or Register  to view this content.

With reference to this thread and sample data...Your output only has 13 columns...What are you wanting for Col 16...What is currently in Col 16...Has your data layout State Code changed?
If you are wanting to amend code to make use of in other scenario then you must at least upload said sample file so one can see!

Come on rajesh...By now you should know this...Not mind readers...

This is a different file I am practicing your code to understand it.
I got the right result with this but your code I need to really learn.

Please Login or Register  to view this content.

If you can tell me what does the T stand for in your formula.

This is a different file I am practicing your code to understand it.

Understood...But like I said...I cannot give you an answer as I don't know what this new data layout looks like...Hence upload a new sample file...

If you can tell me what does the T stand for in your formula.

I told you...it represents TEXT if the lookup was for numbers it would be N

In the file I shared you can please tell me What is T in (VLOOKUP(T(I...? Maybe then I can edit other codes like you do.

Told you twice already...see above...

Ok. Thanks man. I will try again tonight and try to understand. gtg now. Have a nice day.

Geez...all you have to do is supply the new layout and you will then immediately understand...Like I said...I cannot assume to know what this new file looks like...Cannot help you if you aren't prepared to be more transparent...Have told you this multiple times in other threads...
See here...

I am replacing the codes of most of my old applications which are taking around a minute or more. There are more than 30 modules to edit and most of them have sumifs and vlookup formulas. Each time I have to post a sample workbook for the vlookup code. If I understand then I will be able to change the whole code. You are a great help sintek.
I will edit the code of each module and wherever I get stuck I will post the same in a new query each time. If that is ok with you. Will post very soon in a new thread.

Formula:

Please Login or Register  to view this content.

Your formula is looking up the value of B2 in state Col C and getting the result from Col H

This code below is looking up the value of Col 16 in that state code so if Col 16 is empty there is no lookup available...
You need to change the code to lookup the offset from Col 16 which will be Col 2 i.e .offset(,-14).address ... I am assuming...
Not sure what this files setup is...

Please Login or Register  to view this content.

Originally Posted by RAJESH SHAH

Thanks for the update beyond excel.

As always it has been a great pleasure to be able to help you, thanks for the +Rep and do not hesitate to ask any questions you may have.

Thanks beyond excel. The pleasure is all mine. Your words inspire me to accept more challenging problems.

If I understand your setup then this is what you are wanting...

Please Login or Register  to view this content.

sintek. None of the codes you shared is giving me the expected result. I know it is difficult to write without a sample data. Sorry for keeping you guessing. Here is the file for you to check.

sintek. None of the codes you shared is giving me the expected result

What are you referring to Rajesh..You mentioned Col P and the code in post above gives the correct output???
Once again just posting files without explanation...
The codes I supplied for your given files works as they should...Now because you are editing them and trying to use them for other files, now it's my code???

Yes with the code I added. If I replace the P column with your Your below code it s displaying an error.

Please Login or Register  to view this content.

If I replace the P column with your Your below code it s displaying an error.

What does this mean...replace where what...The file above has no replacement anywhere?

To complete the step, I used your one of the codes, edited it and completed it. One formula I was not getting right for column P so I used this code instead and completed it. The file I shared in post #29 is working with this code.

Please Login or Register  to view this content.

Replace that part with your code and check you will see the difference in the result. This one

Please Login or Register  to view this content.

Look at your code above...You changed it from the original I provided...Why add the asteriks

Replace the code I shared in post #33

Please Login or Register  to view this content.

Why you add those...red snippets?

sintek. If it is working on your side, please share the file with the code line to autofit.

Rajesh...Just REMOVE the red asterisks...They must not be there...don't know why you added them...

Aha.. Now it gave me the correct result. I wonder how the red asterisks got into the line. I just copied and pasted the code. No worries. It is good now. Thanks once again. Sintek
New query is ready to post. If you are online please have a look. It is very easy but I think I have missed something. It will be solved in no time.

You give me headaches Rajesh lol...

Sorry man. I really appreciate you trying to make me understand. You can say I am a little slow but passionate to learn and create.

You have been doing well...The best way to learn is to create different scenarios and amend code snippets to accommodate...Just need to explain your requirements better...

Thanks for understanding. I am not just learning to code, but also learning accounts, doing it manually for hours multiple times, see the pattern and work out a plan, start with formulas to get the final result and then write the code and when I get stuck, I take the help from people like you who are always eager to solve💜.