repeatedly extract a field to create a new list in another file?

[nut behind the computer]

I'm trying to set up an excel spreadsheet that I put in addresses in one cell
essentially... and it populates more than one new datasource file (ultimately
this file is used to draw information for a macro to process later) and also
it puts these changing addresses into a running list in another excel file-
basically creating a file that lists all the properties I've worked on in the
last month. It's redundant to copy/paste these addresses and hopefully I
can do something that will populate this running list.

the static link to a specific cell -I can do already. the pull from a cell
and write it in a new cell below the last entry-that escapes me.

 

[Bernie Deitrick]

You need to use a worksheet event - typically, the change event - and VBA
code to place the value of the cell into the list.

For example, since you don't give file names, cell ranges, or sheet names,
you could use something like this in your Entry Sheet 's codemodule to copy
cell A3's value to another workbook - "Data File.xls" - on sheet "Data
Sheet" and at the bottom of column A: Copy this code, right-click the tab of
"Entry Sheet" and select "View Code" and paste the code into the window that
appears. For the code to work as written, the file

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Workbooks("Data File.xls").Worksheets("Data Sheet"). _
Cells(Rows.Count, 1).End(xlUp)(2).Value = Target.Value
Application.EnableEvents = True
End Sub

Change "Data Sheet" to the name of the sheet that you have, and Data File to
the workbook name.

HTH,
Bernie
MS Excel MVP


"nut behind the computer" <nut behind the
(e-mail address removed)> wrote in message
news:[email protected]...