converting a column of names & addresses to a list of rows

[plumeria7]

Hello,

I know nothing about VBA and I used the "record macro" feature in excel to get the following macro:

Sub Macro2()
'

'
' Keyboard Shortcut: Ctrl+m
'
Range("A28:A35").Select
Selection.Copy
Range("C1").Select
Selection.End(xlDown).Select
Range("C5").Select
ActiveWindow.SmallScroll Down:=-3
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.End(xlToLeft).Select
Selection.End(xlToRight).Select
Application.CutCopyMode = False
Selection.Copy
Cells.Find(What:=" Thompson John L", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Application.Run "'noteholder list.xls'!Macro2"
End Sub


Instead of going down the list and copying the names & addresses over - it just does the one name.

Range("A28:A35").Select - This should be selecting the active cell down to the end.

Range("C5").Select - This should be selecting the next empty cell in column "C"

Cells.Find(What:=" Thompson John L", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate - This should be what just was pasted in the last "C" column cell.


I hope this is clearer than mud.

I really would like some help

[kadghar]

Hello,

I know nothing about VBA and I used the "record macro" feature in excel to ...

The macro recorder generates a lot of junk and it wont allow you to move free within the ranges. I can explain you what this macro does, but it'll be easier if you tell us what you want to do so we can help you out with your code.

just a few tips before starting:

Range("A28:A35").Select - This should be selecting the active cell down to the end.

No,this will select the range A28 to A35,

if you use the method END this will move you to the left, right, up or down end of that cell (the same effect as Ctrl + arrow)
so if you want to auto select, lets say column A from 28th row to the down end just do:

range(cells(28,1) , cells(28,1).end(-4121)).select

instead of -4121 or -4161 you can use some excel constants like xldown xltoleft, etc but they'll only work with VBA of Excel (since they're excel constants)

Range("C5").Select - This should be selecting the next empty cell in column "C"

No, this will select cell C5
to select the next empty cell in column C you should use END(-4121) and then move one down.

Cells.Find(What:=" Thompson John L", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate - This should be what just was pasted in the last "C" column cell.

no, here you're searching " Thompson John L" and will select the cell where it finds that text.
to paste use PasteSpecial -4163 (this will paste only the values)

e.g.
to copy the range A5 to its down end and paste it in C5 just do:

Range(cells(5,1), cells(5,1).end(-4121)).copy
cells(5,3).pastespecial -4163

HTH

[plumeria7]

What I am trying to do is this:

I have a list that has the name address city etc in one column as such.

Name
address
city
other info

Name
address
city
other info

Name
address
city
other info

I want to get this information into this format.

name address city other info
name address city other info
name address city other info


As I said before, I don't know VBA, that's why I tried the record macro feature. I tried substituting the code you suggested, however I am missing something because i'm getting compilation errors. I also don't know how to move one cell down.