Cell Reference Macro

Try this. Paste the following code within a macro. Select the first cell with data you want to redo and run the macro, it will put the data on sheet 2.

Here's the code.

Dim name As String
Dim city As String
Dim address As String

num = 1
While Len(ActiveCell) > 1
name = ActiveCell


Sheets(2).Cells(num, 1).Value = name
ActiveCell.Offset(1, 0).Select
city = ActiveCell


Sheets(2).Cells(num, 2).Value = city
ActiveCell.Offset(1, 0).Select
address = ActiveCell

Sheets(2).Cells(num, 3).Value = address
ActiveCell.Offset(1, 0).Select

num = num + 1
Wend

Hi Rene

Here is a fast method that uses the Autofilters.
Run on the sheet that has the names, addresses etc on it.
It assumes your data is in Column A and Column B is blank.
If you have data in Column B then insert a new one.
You will also need a sheet called "Sheet2" in your workbook that has nothing in the first 3 columns.

Sub TryThis()
Dim OldCol As Range, FilterCol As Range
Dim StopCell As String
Set OldCol = Columns(1).SpecialCells(xlCellTypeConstants)
StopCell = Range("A65536").End(xlUp).Offset(0, 1).Address

With OldCol
.Cells(2, 2) = "Name"
.Cells(3, 2) = "Address"
.Cells(4, 2) = "Phone"

Set FilterCol = Columns(2).SpecialCells(xlCellTypeConstants)
FilterCol.AutoFill Destination:= _
Range(.Cells(2, 2).Address & ":" & StopCell)
End With

Set FilterCol = Columns(2).SpecialCells(xlCellTypeConstants)
ActiveSheet.AutoFilterMode = False
With FilterCol
.Cells(1, 1).AutoFilter
.Cells(1, 1).AutoFilter Field:=2, Criteria1:="Name"
OldCol.Offset(1, 0).Copy _
Destination:=Sheets("Sheet2").Cells(2, 1)

.Cells(1, 1).AutoFilter Field:=2, Criteria1:="Address"
OldCol.Offset(1, 0).Copy _
Destination:=Sheets("Sheet2").Cells(2, 2)

.Cells(1, 1).AutoFilter Field:=2, Criteria1:="Phone"
OldCol.Offset(1, 0).Copy _
Destination:=Sheets("Sheet2").Cells(2, 3)

ActiveSheet.AutoFilterMode = False
.Range("A1:A3").Copy
Sheets("Sheet2").Cells(1, 1).PasteSpecial Transpose:=True
End With
FilterCol.Clear
Set FilterCol = Nothing
Set OldCol = Nothing
Application.CutCopyMode = False
End Sub

Dave

OzGrid Business Applications

Thank you verymuch for your help. it really helped me so much. Two last Questions. do i have to include a equal sign at the end of the last cell reference like in the revious codes. for example, address = ActiveCell. and do i have to type "End sub" at the end

Help again please

Gregc your code worked well. i tried to add some cell to the code but it wouldn't work. what is wrong? I keep getting a syntax error with the date line. here is what I typed in.

Dim name As String
Dim address As String
Dim city As String
Dim amount As String
Dim date As String
Dim check number As String

num = 1
While Len(ActiveCell) > 1
name = ActiveCell

Sheets(2).Cells(num, 1).Value = name
ActiveCell.Offset(1, 0).Select
city = ActiveCell

Sheets(2).Cells(num, 2).Value = address
ActiveCell.Offset(1, 0).Select
city = ActiveCell

Sheets(2).Cells(num, 3).Value = city
ActiveCell.Offset(1, 0).Select
amount = ActiveCell

Sheets(2).Cells(num, 4).Value = amount
ActiveCell.Offset(1, 0).Select
date = ActiveCell

Sheets(2).Cells(num, 5).Value = date
ActiveCell.Offset(1, 0).Select
check number = ActiveCell

Sheets(2).Cells(num, 6).Value = check number
ActiveCell.Offset(1, 0).Select

num = num + 1
Wend
End Sub

Sheets(2).Cells(num, 1).Value = name ActiveCell.Offset(1, 0).Select city = ActiveCell

Re: Help again please

Im trying to replicate the code down so that i can get the rest of the original list in the right columns. right now it is putting the next set of data beside the end of the first set. the code reads

Sub Macro1()

Dim name As String
Dim city As String
Dim address As String
Dim amount As String
Dim datee As String
Dim check As String

num = 1
While Len(ActiveCell) > 1
name = ActiveCell

Sheets(2).Cells(num, 1).Value = name
ActiveCell.Offset(1, 0).Select
city = ActiveCell

Sheets(2).Cells(num, 2).Value = city
ActiveCell.Offset(1, 0).Select
address = ActiveCell

Sheets(2).Cells(num, 3).Value = address
ActiveCell.Offset(1, 0).Select
amount = ActiveCell

Sheets(2).Cells(num, 4).Value = amount
ActiveCell.Offset(1, 0).Select
datee = ActiveCell

Sheets(2).Cells(num, 5).Value = datee
ActiveCell.Offset(1, 0).Select
check = ActiveCell

Sheets(2).Cells(num, 6).Value = check
ActiveCell.Offset(1, 0).Select
name = ActiveCell

Sheets(2).Cells(num, 7).Value = name
ActiveCell.Offset(1, 0).Select
city = ActiveCell

Sheets(2).Cells(num, 8).Value = city
ActiveCell.Offset(1, 0).Select
address = ActiveCell

Sheets(2).Cells(num, 9).Value = address
ActiveCell.Offset(1, 0).Select
amount = ActiveCell

Sheets(2).Cells(num, 10).Value = amount
ActiveCell.Offset(1, 0).Select
datee = ActiveCell

Sheets(2).Cells(num, 11).Value = datee
ActiveCell.Offset(1, 0).Select
check = ActiveCell

Sheets(2).Cells(num, 12).Value = check
ActiveCell.Offset(1, 0).Select

num = num + 1
Wend
End Sub

Sheets(2).Cells(num, 1).Value = name ActiveCell.Offset(1, 0).Select city = ActiveCell

Did You try mine ?

Sub Macro1() Dim name As String

Sheets(2).Cells(num, 1).Value = name ActiveCell.Offset(1, 0).Select city = ActiveCell

OzGrid Business Applications

Re: Did You try mine ?

yes i tried it, i couln't get it to work. thanks anyway.

Re: Help again please

Sub Macro1() Dim name As String

Sheets(2).Cells(num, 1).Value = name ActiveCell.Offset(1, 0).Select city = ActiveCell

I emailed you some changes to the code you posted. Let me know if it works.

GREG

New Question

First of all, thanks for your help. I have a new Question. i have two workbook. one has a directory of clients names in one cell and their numbers in another. i have a new workbook that has some of the names on the master list. is there a way have excel look inthe master list and put the number in the new list. any macros that can help. if so what is it. remeber that im really new at this. thanks.

Re: New Question

Do you know how to use lookups? You would need to use a Vlookup, it is a cell function. What it does is looks at the name in sheet2 or whatever sheet that you have with the numberless names and goes to the numbered names sheet, finds the name, takes the number next to it and returns it. Let me know if you need any other help.

GREG

Re: New Question

Thanks, i dont really know much about lookups. ihave tried it. but the data is in a separately-maintained spreadsheets. what i need to do is to get name for a list of numers from the master list that has all the names and number. they are both two diferent files. if VLOOKUP is still the formula to use, can you expain in simple terms what
to put into
"lookup"
"table array"
"col_index_num"
"Range_lookup"

thanks so very much. you are a life saver.

Re: New Question

lookup is the value you want to lookup
-A1 could have the name you want.
Table array is a bunch of cells you want to look in. You have to have the value that you want looked up in the left most column of your cell bunch. Example: name is in column b1 and number is in c1 and you have 50 names in the list, your array would be sheet1!b1:c50
-Column index number is the column in your array that you want to get. Example: The b1:c50 has two columns and the phone number you want is in column 2 of your array.
-Range lookup is either True or False if you want an exact match False, or if you want the closest match True. If you pick true the list you are looking at to retrieve the information has to be sorted in ascending order.

Following is a short lookup that looks up a a name from a1 on the current sheet, goes to sheet 1 and looks at the cells between a1 and a7, finds the value and takes the value in column b and that is your number
=VLOOKUP(A1,Sheet1!A1:B7,2,FALSE)

Did any of that make sense? If not, let me know. You can do it on sheets in different files.