Copy selected columns from Sheet1 to Sheet2 VBA solution required. Not a straight copy.

Hi guys and excel VBA experts.
I need a bit of help here.
I have been successful in adapting code to my needs and did ask for a lot of help in a previous post. It's done in a messey way, but works. I would like to optimise the code once this part is resolved and would appreciate someone looking at my code and optimizing it if they are willing.
However, in this request, I need help with a small portion of the code as I have been able to get it to work if my source is in a specific order, which is not always the case.

So here goes.
The hidden sheet called "Temp" holds data that is pasted from the Windows clipboard. It is all in format Text, just as I need it.
There is another sheet called "Analyse" where only certain columns are copied from the "Temp" sheet.

They are in a defined array ar with the following column names "CRN", "Customer Name", "Circuit/Equip ID", "Extension", "SLA", "Service Type", "Status".
These columns are not allways in the correct order as above in the "Temp" sheet, and there are other colums inbetween these columns too.
The destination columns however need to remain in the order as indicated in the array. I have measures and lookup values in the destination sheet.
A third sheet holds pivot tables that uses the "Analyse" sheet table for some simple measures.

What I need to happen is that when Analyse is run, the desired columns need to be "looked for" in sheet "Temp", copied from row 2 down, and pasted in the defined order in the "Analyse" sheet from row 2 down.
In the "Analyse" sheet there are currently formulas for INDEX and MATCH in columns H, I and J that are using three other sheets to lookup data and fill those cells, but I am working on a solution to add the formula using VBA on the cells, only if column D has a value as that one is "Customer Name" and will never be blank. Suppose I could also just count the rows of data and fill the formula from row 2 down to last data row, and use this for the three formulas.
Also currently my code removes the headers in the "Analyse" sheet as I select the sheet columns A:J and use ClearContents, which then breaks the measures in the Pivot Tables as the table headers are missing. This I need to fix that it clears from row 2 down, but only once I resolve the formula entry with VBA.

I have started on another project where I'm using enums, but I don't quite understand it yet and have other classes starting which is leaving me with little time to figure this request out. I know I will eventually, but I have about 60 people using this spreadsheet and I cannot go to every individual to rearange their desktop app's columns for the source. I would appreciate any help that is provided. Efficient code is a must as I need this to run as fast as possible, which it is not doing too bad at the moment.

I've had to include compatability for older OS and Office versions. Oh and there's a search form that is used on one of the sheets too.

Here follows my code.

Please Login or Register  to view this content.

Hi there, good morning,
I have quite a lot of imports and projects doing similar things.
My suggestion is the following; add an extra worksheet (may be hidden too) where you list your target column's headers in column A and in column B the column they're in (this jus in case you decide to change that order too)
In the columns after that you place the column names of the temp sheet next to the one you want it to be copied to.
let's say Column A is CRM and Column B = 2 the the corresponding column tempsheet header Column C = fieldX

Hav you routine lookup the tempsheets headet and reurn the number in B

If you attach a sample file WITH your macro code and dummy data in target as well as tempsheet I'll try and exp,lain in there in a netter way.

Please Login or Register  to view this content.

Hi, if you can, please upload a sample file(s) with dummy data to reproduce the process. Will make it much easier to 'see' and also test it.
Make sure you have the macro code included (macro embedded file) as well.

Originally Posted by HandSM

@AlphaFrog
This does exactly what my code does. Probably cleaner, but does not paste destination sheet columns in correct order.

This will paste in the order you have the headers in the array in the code.

Please Login or Register  to view this content.