MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help please

  Asked By: Molly    Date: Nov 25    Category: MS Office    Views: 1949

I've tried doing this on my own but coding is a complete mystery to me.

I need to rearrange some database records or reports that have been
copied into a worksheet.

In the worksheet the first row is occupied by what used to be the
database field header, it fills cells A1:H1.
Beneath that are the records and they occupy between 4 and 6 rows per
record. Each row of a record fills cells A:O inclusive. In the
worksheet each record is separated by a single blank row.
Each field header has different text and I need to copy each field
header and add it alongside each of the record rows it relates to.

So, for example, at the top of the worksheet that has a 5 row deep
record pasted into it cells A1:H1 of the first row (the original field
headers) need to be appended to rows 2 to 6 inclusive. This has to be
repeated for each of the records and their individual field headers in
turn until the end of the sheet is reached.

I have done something similar before by simply using the macro
recorder and copying pasting. This time it's way beyond my scope
though because of the varying length of rows in each record.

I've spent about two days trying to work this out but am dog tired
(it's half three in the morning as I write this) and just know that
there's no way I can do this on my own so any help would be very much
appreciated. It'd be useful in the future for me also because I have
similar tasks ahead involving the same sort of problem so a macro that
I can tweak or learn from would be very very handy.



4 Answers Found

Answer #1    Answered By: Haboos Kauser     Answered On: Nov 25

I think we're dealing with a major nomenclature/semantics problem
here. So it makes communication and understanding difficult.

I'll try to describe what it sounds like you're saying:

You have an excel file imported from a database  report.
The report has multiple lines per record.
The number of lines per record  is variable between 4 and 6)
Each record is separated by a blank  row.
The first line of the FILE (Report) is a header  from colunmns A-H (8

Here's where it starts to get confusing:
Each row  of a record fills columns A-O (15 columns)
"Each field  header has different text" ??huh??
You need to add  it "alongside" the record row ??huh??

Then, your example  says something about having 5 rows  of data pasted
into cells  A1-H1 and you want them ADDED to rows 2-6?

Tell you what, can you give us a simple example of a partial record?

for instance,
row1: HDR1 HDR2
row2: DAT1 DAT2
row3: HDR3 HDR4
row4: DAT3 DAT4

row1: HDR1 HDR2 HDR3 HDR4
row2: DAT1 DAT2 DAT3 DAT4

or, send me a sample data set (to Pschreiner at indy.rr.com)
showing the original  data and what the result should be for a single
record and I'll see what I can do.

Answer #2    Answered By: Bama Cohen     Answered On: Nov 25

Please excuse my lack of clarity. Using the HDR and DATA terms of
reference, a typical 6 row  'record' would be in this layout within the

row2: DATA in all cells  to cell O2 inclusive
row3: DATA in all cells to cell O3 inclusive
row4: DATA in all cells to cell O4 inclusive
row5: DATA in all cells to cell O5 inclusive
row6: DATA in all cells to cell O6 inclusive

and there'd be a blank  row in row7 between the record  and the next one
in the worksheet.

Intended result is to have row1 appended to rows  2 to 6 by copying
cells A1:A8 and pasting  to P2:W2 and then copying  that down to P6:W6

The problem  for me is that the records  can be six rows deep (as in the
above example) or five rows or four rows so a simple copy  and paste is
not the best way. There are about a thousand different records in the
worksheet and they each have a single  blank row separating them.

The actual HDRs have various content, ditto the DATA.

Thanks for taking an interest.
Hoping this will clarify rather than add  to the confusion !

Answer #3    Answered By: Hadil Khan     Answered On: Nov 25

OK.. it sounds like you want to add  the the first row  to the end  of each
subsequent row
until there is a break, then do the same for the next set.

I threw together this:
Option Explicit
Sub Hdr_Copy()
Dim Last_Row, CurRow, HdrRow, inx
Dim HdrArray(10)
Last_Row = ActiveCell.SpecialCells(xlLastCell).Row
HdrRow = 1
inx = 0
For CurRow = 1 To Last_Row
If (Cells(CurRow, 1) <> "") Then
If (CurRow = HdrRow) Then
HdrArray(0) = CurRow
For inx = 1 To 8
HdrArray(inx) = Cells(CurRow, inx)
Next inx
For inx = 1 To 8
Cells(CurRow, inx + 15) = HdrArray(inx)
Next inx
End If
HdrRow = CurRow + 1
For inx = 0 To 9
HdrArray(inx) = ""
Next inx
End If
Next CurRow
End Sub
It makes the assumptions that A) the header  row has (8) cells, and B) the data
rows each have (15) fields.

Is that what you're looking for?

Answer #4    Answered By: Dale Jones     Answered On: Nov 25

FWIW the data is salvaged from an old database  lost following a hard
drive failure over a year ago now. Scooting around for a floppy to
backup some current work  I found some Excel workbooks containing data
that had obviously come from the lost database - the data in them is
very useful to me.

I love the efficiency of your macro  and I hope to learn  from it.
Thanks again,

Didn't find what you were looking for? Find more on Help please Or get search suggestion and latest updates.