Excel Macro: copy paste certain columns to...

Hi Rizvisa1 - pls find the file at following link
https://accounts.google.com/ServiceLogin?service=wise&passive=1209600&continue=https://docs.google.com/spreadsheets/d/1dNiH0-pwsUh40xD4rAshWuUUk1dhERbAsjB6FhT6YSk/edit?authkey%3DCLCrkyI%26hl%3Den%26authkey%3DCLCrkyI%26hl%3Den&followup=https://docs.google.com/spreadsheets/d/1dNiH0-pwsUh40xD4rAshWuUUk1dhERbAsjB6FhT6YSk/edit?authkey%3DCLCrkyI%26hl%3Den%26authkey%3DCLCrkyI%26hl%3Den<mpl=sheets&hl=en#gid=0
(I deleted the rest of the columns (could go to +500,000 lines)

What I need is excel to look for certain columns (e.g. client, customer, amount,...), create a new worksheet with a certain name e.g. BSID and then put the columns in the order how I want it...

Thanks a lot!

I am still not able to understand what you are looking for. Looking for certain column ? what you meant by that

Hi Rizvisa, sorry for that.

What I want is that, for my analysis I can base myself always on the same set of columns in the same order.

When I receive my data, the columns with the data I need are in there, but they are not necessarily always at the same place.... e.g. customer numbers can be in column C or D or F, so I want excel to look for those columns I need, and copy all the data of those columns into a new sheet on which I can run macro's on then...

hope this is a bit more clear....

Thanks!
Ellen


What I have is this macro which looks for column A and then copies it to another sheet to a certain place:


----------------------------
Sub ComplileData()
With Sheets("KNKK")
.Range(.Range("A1"), .Range("A1048576").End(xlUp)).Copy
End With
Sheets("Data").[L1048576].End(xlUp)(2).PasteSpecial Paste:=xlValues

With Sheets("KNKK")
.Range(.Range("B1"), .Range("B1048576").End(xlUp)).Copy
End With
Sheets("Data").[A1048576].End(xlUp)(2).PasteSpecial Paste:=xlValues
End Sub
------------------------------

However, what is missing here is that:
1. the macro looks for a column at a certain spot (column A) but I need it to look for column with header e.g. customer, amount,... (all headers are in row #1)
2. the macro is not creating itself a new sheet and copying everything to there... now I need to make myself a sheet with the name "data"

Thanks a lot

So what you need to do is first find out the column location. Then you can do the copy

Dim Cell as Range
Dim iCol as integer
Set Cell = Sheets("KNKK").Range("1:1").Find("Customer",,,xlWhole)

If Cell is Nothing then
msgbox "Unable to find Customer"
exit sub
else
iCol = Cell.Column
End If

With Sheets("KNKK")
.Range(.Cells(1, iCol), .Cell(Rows.count, iCol).End(xlUp)).Copy
End With