Create copy of sheet using VB with columns in different order

I have a data sheet with column headings. I have a second sheet with
column A containing the preferred order of the columns and only the
needed columns. This can be changed to just be column headings if it's
easier.

I want to make a third sheet (or use the second one if we just use
column headings) with the columns of data taken from the first sheet
in the right order, skipping any columns not on the list and somehow
letting me know if a required column didnn't exist on the first data
sheet. Make sense? Please help me get started on this. I'm still
somewhat new to VB as I do about one little task with it every year or
two.

I'm gonna guess that you have headings in A1:Axx (on that column heading
sheet). And A1 is the header for column A, A2 for B, A3 for C, ...

Option Explicit
Sub testme()

Dim HeaderWks As Worksheet
Dim DataWks As Worksheet
Dim NewWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim FoundCell As Range
Dim oCol As Long

Set HeaderWks = Worksheets("sheet1")
Set DataWks = Worksheets("sheet2")
Set NewWks = Worksheets.Add

With HeaderWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

oCol = 0
For Each myCell In myRng.Cells
With DataWks.Rows(1)
Set FoundCell = .Cells.Find(What:=myCell.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlNext, _
searchdirection:=xlByColumns, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
MsgBox "Design error with: " & myCell.Value
Else
oCol = oCol + 1
FoundCell.EntireColumn.Copy _
Destination:=NewWks.Cells(1, oCol)
End If
Next myCell

End Sub

(If you have the headers in column A and the order in column B, then sort by
column B to put it in nice order for the macro first.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Bryan wrote:
>
> I have a data sheet with column headings. I have a second sheet with
> column A containing the preferred order of the columns and only the
> needed columns. This can be changed to just be column headings if it's
> easier.
>
> I want to make a third sheet (or use the second one if we just use
> column headings) with the columns of data taken from the first sheet
> in the right order, skipping any columns not on the list and somehow
> letting me know if a required column didnn't exist on the first data
> sheet. Make sense? Please help me get started on this. I'm still
> somewhat new to VB as I do about one little task with it every year or
> two.

--

Dave Peterson