from lists to databases

  • Thread starter Thread starter vanderwolluc-jean
  • Start date Start date
V

vanderwolluc-jean

Hi, it is easy to mailmerge database items from an excel document into
a, say, data labels in a word document. Is there an easy way, other
than pasting and transposing, to do it in the opposite way? That is :
to create long lists of line items (e.g. 1st line = name; next line =
address; next line = place) to be transferred to a standard excel
database (columns as fields : 1st column = name; 2nd column = address;
third column = place)? Thanks. Jean.
 
Jean

No easy way to do it manually but it could be done fairly simply with VBA.
It depends if there are always say 5 lines to an entry or whether we would
need to look for a space in between.

Let us know

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Thanks for the swift reaction. Your question on the number of lines is
indeed important. In many cases there are indeed not a standard number
lines, e.g. in the example of addresses, some have a company name in
front of the name, others don't.
Regards, Jean.
 
Jean

Do they all have a space between each name/address combination?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Jean

If the list is in column A, the code below should work. It adds a sheet and
transposes all the data to this sheet. I am a little busy to test
thoroughly so test on a copy first.

Sub MoveData()
Dim lRowStart As Long, lRowEnd As Long
Dim lLastRow As Long, x As Long
Dim newSht As Worksheet, oldSht As Worksheet
Set oldSht = ActiveSheet
Set newSht = Worksheets.Add
oldSht.Activate
lLastRow = oldSht.Range("A65536").End(xlUp).Row
lRowStart = 1
x = 1
Do While lRowEnd < lLastRow
lRowEnd = oldSht.Range("A" & lRowStart).End(xlDown).Row
oldSht.Range(Cells(lRowStart, 1), Cells(lRowEnd, 1)).Copy
newSht.Range("A" & x).PasteSpecial Transpose:=True
lRowStart = lRowEnd + 2
x = x + 1
Loop
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick, it works very well. I tried it with a list of 60 datagroups
containing 7 fields (lines each) and it was all OK. Thanks.
Jean.
 
Back
Top