I need column data NOT in A,B,C,D order when copying to a new sheet.

[PitBoo]

I am using the following piece of code from another workbook. It basically transfers some data from one sheet into another sheet.
It works good in the other workbook because it copies data from certain rows/columns and pastes them (the first sheet has like 20 columns and I only need data from 4 of them). And it puts them in order.

Problem is the original workbook I borrowed this from by default takes the first 4 columns (A,B,C,D) and transfers them to the target sheet the same way (A,B,C,D). With the new sheet I need column data NOT in A,B,C,D order


Here is the original line in the code:

Please Login or Register  to view this content.

I changed it to this:

Please Login or Register  to view this content.

Problem is it still wants to bring those columns over in alphabetical cell column letter (A,B,I,K), when I need it in the order (A,I,K,B)



Here is the entire code I am using.

Please Login or Register  to view this content.

Thanks

[XLent]

edit: disregard below - it will not return the row values correctly with the column array (rather it will repeat first row element); apologies

if you only require values you could change tack a little, and leverage functionality of index - e.g.

Please Login or Register  to view this content.

[XLent]

You could, of course, just copy each column in turn, i.e.:

Please Login or Register  to view this content.

[PitBoo]

Thanks for the reply,

For this one it is throwing a Compile Error at the first av1

Capture2.JPG


This is how a put it together, I probably put something in the wrong spot.

Please Login or Register  to view this content.

I used the code in your second post below and it worked great, everything fell in line. The only two issues were that it brought over all of the conditional formatting rules from the first sheet and the A cell on the first sheet has drop down options and when it copied it pulled all of the drop down options for that cell.
Everything copied over correctly but those were just two small things. That's why I took a second look at this code because you stated to use it if I only require VALUES, which would be ideal.


Thanks again.

[XLent]

Re: 1st error - yes, if running Option Explicit (good practice) then you'd need to declare av1, i.e.

Please Login or Register  to view this content.

Re 2nd approach -- if you just want values you can use PasteSpecial etc or, making use of av1 (created per first example) you could use the below approach:

Please Login or Register  to view this content.

the above will not reset the formatting / validation already in place so if unwanted you'd need to remove first, as a one-off.

[PitBoo]

Ok this is what I am using now.

Please Login or Register  to view this content.

The only two issues are:
1. Its copying over conditional formatting from the first sheet
2. Its bringing along data validation drop downs from the first sheet.

So I am assuming I would just need some sort of pastespecial code or something?

Problem is I have tried to figure it out on my own with no luck (I can get by with modifying some code for the most part but creating anything new and I am at my limits of skill).


I can LIVE with the above 2 issues if need be but would be grateful if someone could help me fix it.

[XLent]

So, without rewriting too much, and further to prior posts, you might try using the below:

Please Login or Register  to view this content.

as mentioned, you will need to clear any pre-existing (& unwanted) formatting/validation from 'wo cover' worksheet as the above code will simply clear & copy contents.