how would you do this?

[Greg Henderson]

Hello

I have stripped date from a legacy system to a .xls in this format
....customer # and approx 20 blocks of data -- all running to the
right. Each block is 6 columns ( date- invoice - note - charge -
credit & balance)

How do I take the blocks and paste both the customer number from
column A below and then paste the series of customer data ..and keep
doing this untill all the data is in a 7 column data and not strung
out to the right.

Is there an macro wat to do this? That might be a new years
resolution to learn more on macro's?

Thanks for any help offered

Regards

Greg H
ps- please remove NOSPAM from e-mail or post back here for all

 

[J.E. McGimpsey]

Hello

I have stripped date from a legacy system to a .xls in this format
...customer # and approx 20 blocks of data -- all running to the
right. Each block is 6 columns ( date- invoice - note - charge -
credit & balance)

How do I take the blocks and paste both the customer number from
column A below and then paste the series of customer data ..and keep
doing this untill all the data is in a 7 column data and not strung
out to the right.

Is there an macro wat to do this? That might be a new years
resolution to learn more on macro's?

I'm a bit foggy on both your original and your desired data layouts.
It sounds like your original data has A1Q1 (121 Columns) filled
with one record, and you want that data put into A1:G20 - of a
separate sheet? Then the second record immediately below that?

If so, this should work for you - it's not really elegant, but it
works.

Public Sub RearrangeData()
Dim rSource As Range
Dim rDest As Range
Dim rCell As Range
Dim i As Long

Set rDest = Sheets("Sheet2").Range("A1")
Set rSource = Sheets("Sheet1").Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
For Each rCell In rSource
With rCell
For i = 1 To Cells(.Row, _
Columns.Count).End(xlToLeft).Column - 1 Step 6
rDest.Value = .Value
rDest.Offset(0, 1).Resize(1, 6).Value = _
.Offset(0, i).Resize(1, 6).Value
Set rDest = rDest.Offset(1, 0)
Next i
End With
Next rCell
End Sub


If not, post back with more detail.