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. |
Create copy of sheet using VB with columns in different order
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 |
All times are GMT +1. The time now is 04:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com