View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
joel joel is offline
external usenet poster
 
Posts: 9,101
Default Move form results to next blank row in diff worksheet

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