Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup ? Match? Something else
I have a spreadsheet that consists to 2 worksheets. Worksheet 1 is my "live"
data. Worksheet 2 unparsed customer data. I want to selectively move data from worksheet 2 into columns in worksheet 1. My problem - this will vary from customer to customer. What I would like to do is to put a number, from 1- 15, at the top of the column that I want to select and have a macro(code?) move any selected columns to the corresponding columns in sheet 1. For example, I have identified 3 columns in sheet 2 that I want to move to sheet 1. At the top of these columns I enter 1, 2 and 3. When I run the macro, these columns are moved to columns 1,2 and 3 in worksheet 1. My next customer has 5 columns that I wish to move. Worksheet 2 column headings have 1,2,3,4,5 which the macro will move. These columns that I will identify are NOT static. In the first example, 1 may start in A1, 2 in D1 and 3 in J1. In the second example, 1 may start in J1, 2 in K1 and 3 in X1. Is there any way to do this programatically????? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup ? Match? Something else
This might be a start:
rw1 will hold the row number on Sheet1 where you want the data rw2 will hold the row number on Sheet2 where the data to be copied is Dim rw1 as Long, rw2 as Long, i as Long Dim rng as Range, cell as Range rw1 = 21 rw2 = 35 With Worksheets("Sheet2") set rng = .Range(.cells(1,1),.cells(1,256).End(xltoLeft)) End with for each cell in rng if isnumeric(cell) then i = cell.value worksheets("Sheet1").Cells(rw1,i).Value = _ worksheets("sheet2").Cells(rw2,cell.Column).Value end if Next -- Regards, Tom Ogilvy "Andre726" wrote in message ... I have a spreadsheet that consists to 2 worksheets. Worksheet 1 is my "live" data. Worksheet 2 unparsed customer data. I want to selectively move data from worksheet 2 into columns in worksheet 1. My problem - this will vary from customer to customer. What I would like to do is to put a number, from 1- 15, at the top of the column that I want to select and have a macro(code?) move any selected columns to the corresponding columns in sheet 1. For example, I have identified 3 columns in sheet 2 that I want to move to sheet 1. At the top of these columns I enter 1, 2 and 3. When I run the macro, these columns are moved to columns 1,2 and 3 in worksheet 1. My next customer has 5 columns that I wish to move. Worksheet 2 column headings have 1,2,3,4,5 which the macro will move. These columns that I will identify are NOT static. In the first example, 1 may start in A1, 2 in D1 and 3 in J1. In the second example, 1 may start in J1, 2 in K1 and 3 in X1. Is there any way to do this programatically????? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup ? Match? Something else
Maybe this would be a better example:
if <any column sheet 2 contains the number 1 copy contents of that column to column 21 in sheet 1 if (any column sheet 2 contains the number 2 copy contents of that column to column 22 in sheet 1 .... .... if ,any column sheet 2 contains the number 25 copy contents of that column to column 46. The first cell in each column is the only one to be interrogated. I am looking for a numeric value from 1 - 25. If found, copy, otherwise ignore that column. Thanks. "Tom Ogilvy" wrote: This might be a start: rw1 will hold the row number on Sheet1 where you want the data rw2 will hold the row number on Sheet2 where the data to be copied is Dim rw1 as Long, rw2 as Long, i as Long Dim rng as Range, cell as Range rw1 = 21 rw2 = 35 With Worksheets("Sheet2") set rng = .Range(.cells(1,1),.cells(1,256).End(xltoLeft)) End with for each cell in rng if isnumeric(cell) then i = cell.value worksheets("Sheet1").Cells(rw1,i).Value = _ worksheets("sheet2").Cells(rw2,cell.Column).Value end if Next -- Regards, Tom Ogilvy "Andre726" wrote in message ... I have a spreadsheet that consists to 2 worksheets. Worksheet 1 is my "live" data. Worksheet 2 unparsed customer data. I want to selectively move data from worksheet 2 into columns in worksheet 1. My problem - this will vary from customer to customer. What I would like to do is to put a number, from 1- 15, at the top of the column that I want to select and have a macro(code?) move any selected columns to the corresponding columns in sheet 1. For example, I have identified 3 columns in sheet 2 that I want to move to sheet 1. At the top of these columns I enter 1, 2 and 3. When I run the macro, these columns are moved to columns 1,2 and 3 in worksheet 1. My next customer has 5 columns that I wish to move. Worksheet 2 column headings have 1,2,3,4,5 which the macro will move. These columns that I will identify are NOT static. In the first example, 1 may start in A1, 2 in D1 and 3 in J1. In the second example, 1 may start in J1, 2 in K1 and 3 in X1. Is there any way to do this programatically????? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
second or third match in vlookup() or Match() | Excel Worksheet Functions | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions |