Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Sheet & Copy Stuff | Excel Worksheet Functions | |||
macro to create, name sheet and copy. | Excel Discussion (Misc queries) | |||
copy columns with order reversed | Excel Discussion (Misc queries) | |||
How do I create a fundraiser order sheet? | Excel Discussion (Misc queries) | |||
How to create a Macro to Copy Information in one sheet to another sheet. | Excel Programming |