to find the next open row in you data (assume the sheet with the cleaned up
data is named Data)

set rng = Worksheets("Data").Cells(rows.count,"A").End(xlup) (2)
rng.Value = sLastName
rng.offset(0,1).value = sFirstName
rng.offset(0,2).Value = sOffice

and so forth

--
Regards,
Tom Ogilvy


"Dee" wrote in message
...

I've solved the first problem (i.e. the reduplication of the first
record's
content over and over again.).

Now I just need to figure out how to append the output list each time I
pull
a record in and parse it down to what I need to retain.

My parsed output is all text strings, contained in different A cells where
I've embedded the formulas necessary to parse out the info.

For instance, in cell A38 I have the last name from the record: Doe
In cell A14 I have the email parsed from the record:
In cell A18 I have Office Address
In cell A25 I have the office phone: (123) 456-7890
In cell A28 I have the first name: John
and so on down the A column

The output needs to be arrayed in a table across columns headed as
follows:

A40: Last Name
B40: First Name
C40: Office
D40: Company Address
E40: City and Zip
F40: Phone
G40: Fax
H40: Email address

with the first record's information appearing starting in A41....H41.

In the Excel macro I recorded I attempted to follow Lotus 123 macro
language
where you can designate range names and then direct the cursor to the
first
blank row in a list by {GOTO} rangename and then {END}{DOWN} {DOWN} type
commands. However, my Excel macro does not take me to first blank line at
the
end of my record list like a 123 {END}{DOWN}{DOWN} would...instead it
keeps
taking me to the literal cell that got recorded when I recorded the
keystrokes.

Here's what I recorded:

Sub copy()
'
' copy Macro
' Macro recorded 11/28/2005 by McHenry
'
' Keyboard Shortcut: Ctrl+c
'
Application.CutCopyMode = False
Range("A14").Select
Selection.copy
Application.Goto Reference:="email_address"
Selection.End(xlDown).Select
Range("H42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A18").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="Company_Address"
Selection.End(xlDown).Select
Range("D42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A19").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="Office"
Selection.End(xlDown).Select
Range("C42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A21").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="City_and_Zip"
Selection.End(xlDown).Select
Range("E42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A24").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="fax"
Selection.End(xlDown).Select
Range("G42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A25").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="phone"
Selection.End(xlDown).Select
Range("F42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A28").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="First_Name"
Selection.End(xlDown).Select
Range("B42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A35").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="Last_Name"
Selection.End(xlDown).Select
Range("A42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A5").Select
End Sub


Thanks K Dales!


"K Dales" wrote:

Can you show your existing macro? Hard to answer without seeing it.
Also
what does the input text look like? How do you want the output to look?
--
- K Dales


"Dee" wrote:

I am attempting to write a macro that will edit subsequent rows of
data (text
strings always in the same format for each row) which I paste in from
another source one group at a time, and place the portions I wish to
retain
elsewhere in my spreadsheet in the form of a mailing list. In other
words, I
want to: #1 copy text string data from another source (which is always
laid
out in the same format for each record); #2 paste the data into my
spreadsheet; #3 run a macro which pulls out only those portions I need
to
retain and places those portions elsewhere in my spreadsheet one
record after
another; and #4: start the process over with the next group of data
pasted in
on top of the first group.

I have two problems: 1) While I have written a macro that
successfully
edits the first group of pasted data text strings, when I pull in a
subsequent set of data strings and paste them over the first set of
data and
then run the macro, the results returned are always those results
obtained
from the very first set of data strings. 2) I am not clear on how
best to
copy the parsed -out portions of each record to the mailing list
portion of
my spreadsheet -- that is, I do not know how to append the mailing
list
portion of my spreadsheet with each new parsed-out record.

What I am trying to avoid is having to pull every record in from my
source
at one time and THEN run the macro to edit out the portions of each
record I
wish to retain. I want to do it one record at a time--paste in one
record
group, edit down and copy to elsewhere what I need to retain from the
record
utilizing a macro, go back to my source, copy the next record group
and paste
it into my spreadsheet over the first record group, run the editing
and copy
macro(s), go back to the source for the next group, etc.

The source copy and subsequent pasting into Excel results in the data
ending
up in cells A1, B1. C1, etc. with alot of extraneous text and spaces I
don't
need. After the paste, it looks like this:

A1: John D Doe Company Title
B1: See descriptions... Other Titles
C1: See descriptions... CompanyABC Inc.
D1: Webpage Address123 Anystreet, Suite 2
E1: Anytown, USA 12345 Phone(123)456-7890 Ext. 0 Fax(123)456-7980

Can anyone help me please?

Thanks!