Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting header rows with macro.
I would like to create a macro that selects certain "Columns" based on a
header name in an excel file. It would then copy all of that information except the header into another existing excel file under different header names. something like this: In workbook 'A' I have 4 columns - "Name", "Description", "Price" and "Quantity" I want to select all info under the headers "Name" and "Price" it would then copy all of that information to workbook 'B'. Any info that was under "Name" would go under the header "Item" and any info that was under "Price" would go under the header "MSRP". I have no problem using macro to copy from one workbook to the next, but I do have a problem selecting multiple Columns because with the files that I get, the columns are not always in the same place. If anyone has a solution to my problem I would be very thankful. It would shave off 6 hours of tedious manual work so I could focus on other projects. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting header rows with macro.
I would put on a seperate worksheet the old column Name (book A)in Column A
and the new column name (Book B) in column B. This should get you started. the use the following loop RowCount = 1 with workbooks("A") with .sheets("Column Names") do while .Range("A" & RowCount) < "" OldCol = .Range("A" & RowCount) NewCol = .Range("B" & RowCount) with .sheets("Data") set c = .Row(1).find(what:=OldCol, _ lookin:=xlvalues,lookat:=xlwhole) if c is nothing then msgbox("Cannot find Column : " & OldCol) exit sub else LastRow = .cells(Rows.Count,c.column).end(xlup).Row Set CopyRange = .Range(c.offset(1,0),.cells(LastRow,c.column)) end if end with with workbooks("B").sheets("Data") set c = .Row(1).find(what:=NewCol, _ lookin:=xlvalues,lookat:=xlwhole) if c is nothing then msgbox("Cannot find Column : " & NewCol) exit sub else CopyRange.copy Destination:=c.offset(1,0) end if end with RowCount = RowCount + 1 loop end with end with "John M." wrote: I would like to create a macro that selects certain "Columns" based on a header name in an excel file. It would then copy all of that information except the header into another existing excel file under different header names. something like this: In workbook 'A' I have 4 columns - "Name", "Description", "Price" and "Quantity" I want to select all info under the headers "Name" and "Price" it would then copy all of that information to workbook 'B'. Any info that was under "Name" would go under the header "Item" and any info that was under "Price" would go under the header "MSRP". I have no problem using macro to copy from one workbook to the next, but I do have a problem selecting multiple Columns because with the files that I get, the columns are not always in the same place. If anyone has a solution to my problem I would be very thankful. It would shave off 6 hours of tedious manual work so I could focus on other projects. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Rows with Macro | Excel Programming | |||
Macro selecting rows. | Excel Programming | |||
selecting multiple rows within a Macro | Excel Programming | |||
selecting multiple rows within a Macro | Excel Programming | |||
selecting multiple rows within a Macro | Excel Programming |