I have a worksheet (Input) that uses several drop down menus to make
selections that result in corresponding data showing up in the adjacent
column. i.e. (A1) process selected from list of 9 options then (B1) returns
production rate of process. (A2) process selected from list of 9 options then
(B2) returns production rate of process and so on until all required
processes are selected for project.
On another worksheet (OPS) I have columns for every process that I want to
use to create a dynamic schedule using a Gantt chart. I want to populate this
worksheet with the selected data from the "Input" worksheet.
I used the "Match" function to identify the selected process with the
appropriate column in the (OPS) worksheet but can't figure out how to
associate the selection and corresponding data in order to move it to the
first blank row of the "OPS" spreadsheet.

--
dhunter43
The Performance Advantage, LLC

funcxtions should be used to write to other locations. Functions only get
data from other locations.

The best way to achieve your requirements is to us a worksheet_change macro.
The macro can be written such when you enter new information in one
spreadsheet it automatically writes the data in another location.

"dhunter43" wrote:

I have a worksheet (Input) that uses several drop down menus to make
selections that result in corresponding data showing up in the adjacent
column. i.e. (A1) process selected from list of 9 options then (B1) returns
production rate of process. (A2) process selected from list of 9 options then
(B2) returns production rate of process and so on until all required
processes are selected for project.
On another worksheet (OPS) I have columns for every process that I want to
use to create a dynamic schedule using a Gantt chart. I want to populate this
worksheet with the selected data from the "Input" worksheet.
I used the "Match" function to identify the selected process with the
appropriate column in the (OPS) worksheet but can't figure out how to
associate the selection and corresponding data in order to move it to the
first blank row of the "OPS" spreadsheet.

--
dhunter43
The Performance Advantage, LLC

Thank you for your guidance but I need additional information as I've never
written a worksheet_change macro before. In fact I am just learning how to
use & edit macro's so as much additional information/instruction would be
appreciated.
--
dhunter43
The Performance Advantage, LLC


"Joel" wrote:

funcxtions should be used to write to other locations. Functions only get
data from other locations.

The best way to achieve your requirements is to us a worksheet_change macro.
The macro can be written such when you enter new information in one
spreadsheet it automatically writes the data in another location.

"dhunter43" wrote:

I have a worksheet (Input) that uses several drop down menus to make
selections that result in corresponding data showing up in the adjacent
column. i.e. (A1) process selected from list of 9 options then (B1) returns
production rate of process. (A2) process selected from list of 9 options then
(B2) returns production rate of process and so on until all required
processes are selected for project.
On another worksheet (OPS) I have columns for every process that I want to
use to create a dynamic schedule using a Gantt chart. I want to populate this
worksheet with the selected data from the "Input" worksheet.
I used the "Match" function to identify the selected process with the
appropriate column in the (OPS) worksheet but can't figure out how to
associate the selection and corresponding data in order to move it to the
first blank row of the "OPS" spreadsheet.

--
dhunter43
The Performance Advantage, LLC

try this code. worksheet change only works on the sheet where the code is
placed. VBA has module and sheets. This belongs on the sheet page.

1) go to excel worksheet and right click tab on bottom (normally sheet1)
2) select view code
3) paste this code on worksheet.
4) any number in column E (5) will get put on sheet 2.

Sub worksheet_change(ByVal Target As Range)
For Each Cell In Target
If Cell.Column = 5 Then

With Sheets("Sheet2")
LastRowColC = .Cells(Rows.Count, "C").End(xlUp).Row
If (LastRowColC < 1) Or _
(Not IsEmpty(.Cells(LastRowColC, "C"))) Then
LastRowColC = LastRowColC + 1
End If
.Cells(LastRowColC, "C") = Cell
End With
End If
Next Cell
End Sub


"dhunter43" wrote:

Thank you for your guidance but I need additional information as I've never
written a worksheet_change macro before. In fact I am just learning how to
use & edit macro's so as much additional information/instruction would be
appreciated.
--
dhunter43
The Performance Advantage, LLC


"Joel" wrote:

funcxtions should be used to write to other locations. Functions only get
data from other locations.

The best way to achieve your requirements is to us a worksheet_change macro.
The macro can be written such when you enter new information in one
spreadsheet it automatically writes the data in another location.

"dhunter43" wrote:

I have a worksheet (Input) that uses several drop down menus to make
selections that result in corresponding data showing up in the adjacent
column. i.e. (A1) process selected from list of 9 options then (B1) returns
production rate of process. (A2) process selected from list of 9 options then
(B2) returns production rate of process and so on until all required
processes are selected for project.
On another worksheet (OPS) I have columns for every process that I want to
use to create a dynamic schedule using a Gantt chart. I want to populate this
worksheet with the selected data from the "Input" worksheet.
I used the "Match" function to identify the selected process with the
appropriate column in the (OPS) worksheet but can't figure out how to
associate the selection and corresponding data in order to move it to the
first blank row of the "OPS" spreadsheet.

--
dhunter43
The Performance Advantage, LLC

Joel,

The code worked well except I have a couple additional wrinkles that I
probably didn't explain very well.
1) The code didn't work on the cells that use the VLOOKUP function to
populate the cell with data
2) I need to transpose the data from rows to columns in a single row on the
2nd worksheet but the columns are not necessarily adjacent. There may be
several blank cells between columns that need to be populated. I have a
"named range" in a seperate worksheet that uses the "MATCH" function to
identify the corresponding columns but don't know how to use it to transfer
and transpose the data.

I appreciate your patience and assistance.

--
dhunter43
The Performance Advantage, LLC


"Joel" wrote:

try this code. worksheet change only works on the sheet where the code is
placed. VBA has module and sheets. This belongs on the sheet page.

1) go to excel worksheet and right click tab on bottom (normally sheet1)
2) select view code
3) paste this code on worksheet.
4) any number in column E (5) will get put on sheet 2.

Sub worksheet_change(ByVal Target As Range)
For Each Cell In Target
If Cell.Column = 5 Then

With Sheets("Sheet2")
LastRowColC = .Cells(Rows.Count, "C").End(xlUp).Row
If (LastRowColC < 1) Or _
(Not IsEmpty(.Cells(LastRowColC, "C"))) Then
LastRowColC = LastRowColC + 1
End If
.Cells(LastRowColC, "C") = Cell
End With
End If
Next Cell
End Sub


"dhunter43" wrote:

Thank you for your guidance but I need additional information as I've never
written a worksheet_change macro before. In fact I am just learning how to
use & edit macro's so as much additional information/instruction would be
appreciated.
--
dhunter43
The Performance Advantage, LLC


"Joel" wrote:

funcxtions should be used to write to other locations. Functions only get
data from other locations.

The best way to achieve your requirements is to us a worksheet_change macro.
The macro can be written such when you enter new information in one
spreadsheet it automatically writes the data in another location.

"dhunter43" wrote:

I have a worksheet (Input) that uses several drop down menus to make
selections that result in corresponding data showing up in the adjacent
column. i.e. (A1) process selected from list of 9 options then (B1) returns
production rate of process. (A2) process selected from list of 9 options then
(B2) returns production rate of process and so on until all required
processes are selected for project.
On another worksheet (OPS) I have columns for every process that I want to
use to create a dynamic schedule using a Gantt chart. I want to populate this
worksheet with the selected data from the "Input" worksheet.
I used the "Match" function to identify the selected process with the
appropriate column in the (OPS) worksheet but can't figure out how to
associate the selection and corresponding data in order to move it to the
first blank row of the "OPS" spreadsheet.

--
dhunter43
The Performance Advantage, LLC

Hi dhunter43

Maybe this page is useful
http://www.rondebruin.nl/copy1.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"dhunter43" wrote in message ...
I have a worksheet (Input) that uses several drop down menus to make
selections that result in corresponding data showing up in the adjacent
column. i.e. (A1) process selected from list of 9 options then (B1) returns
production rate of process. (A2) process selected from list of 9 options then
(B2) returns production rate of process and so on until all required
processes are selected for project.
On another worksheet (OPS) I have columns for every process that I want to
use to create a dynamic schedule using a Gantt chart. I want to populate this
worksheet with the selected data from the "Input" worksheet.
I used the "Match" function to identify the selected process with the
appropriate column in the (OPS) worksheet but can't figure out how to
associate the selection and corresponding data in order to move it to the
first blank row of the "OPS" spreadsheet.

--
dhunter43
The Performance Advantage, LLC