Copy Columns Based on Header matching a variable to another sheet in VBA

[weaselmcguff]

We have a rather large file that exports 256 columns of data. We want to use VBA to copy 14 of the columns to another sheet. The columns are scattered through out the entire workbook. I have the code created to create the new sheet. Now I am trying to figure out how to copy the entire column to the new sheet. Problem is the columns are never in the same order when the file is exported. Today Customer could be column A tomorrow it is d.

some of the columns we are trying to copy are
Customer, Parts, Action, Area, Owner, Standard Fault, Qty, TI, TU, etc

Any suggestions?

 

[Miratshah]

I have a similar question where in I have two worksheets (Sheet1 & Sheet2) from where I need to copy + paste few columns in another worksheet. The problem is there are separate columns in each worksheets however they reach same column in destination worksheet. I have included an example for you for more understanding. Please see below:-

	A	B	C
1	WorksheetName	SheetColumnName	FinalFileColumnName
2	Sheet1	Agency GSTIN	Agency GSTIN
3	Sheet1	Type	Invoice/CN
4	Sheet1	Reporting Code	Reporting Code
5	Sheet2	Type	Invoice/CN
6	Sheet2	Company Code	Company No.
7	Sheet2	Agency GST Number	Agency GSTIN

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

 

[EleKTron]

You are welcome - thanks for the reply.

Hello Joe,

after much search found your solution which really helped me as well
thanks for that !

i would like to sort one particular column but the data should be reshuffled accordingly
i tried the record macro option but i am unable to adapt it to my sheet

Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 Key:=Range("B2:B13" _
), SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet11").Sort
.SetRange Range("A1:C13")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

i want to use this function but with 2 adaptations

1. the data should not take the 1st row into account as it is header
2. the data from column B should be sorted from B2 to the end (this last value may vary on file to file basis hence cannot specify the range )

Could you please help me with this

 

[JoeMo]

This thread is 6 years old.
You need to start a new thread with a detailed explanation of what you want to achieve. If possible post some representative sample data using XL2BB.

 

[EleKTron]

This thread is 6 years old.
You need to start a new thread with a detailed explanation of what you want to achieve. If possible post some representative sample data using XL2BB.

Thanks for the reply Joe
I did start a seperate thread for my initial issue
got no replies until now
will create another thread again
hopefully this time i shall get help