I'm not sure of the layout of your source data exactly -- but you seem
to have worked out the extracting of the data anyway.

Assuming that all data is in one A1 for client1 and A2 for client2 etc.
Something like the following should point you in one direction
(continuing your approach):

Sub ExtractDataFromActiveSheetAndWriteToNewSheet()

Dim ClientArray() As Variant
DIm ClientCount as long
Dim intClient As Integer
Dim intData As Integer
Dim shSOurce as worksheet
Dim wbDest as workbook, shDest as worksheet

set shsource = activesheet

ClientCount = shsource.usedrange.rows.count

'I would use a zerobound array but this will do fine
ReDim ClientArray(1 To ClientCount, 1 To 16)

'Go through each row (assuming one row/client)
For intClient = 1 To ClientCount

'get the data for this client - I don't know
'how you get it out, but let's pretend it's
'fixed length (20 char each) strings for simplicity
For intData = 1 to 16
ClientArray(intClient, intData) _
= MID(shsource.cells(intClient,1), _
(intData -1) * 20 + 1, 20)
Next intdata

Next intClient 'Now do the next client

'dump our retrieved data to a new workbook
'(quicker to do this in one fell swoop rather
'than line by line)

set wbdest = workbooks.add
set shdest = wbdest.sheets(1)

With shDest
.Range(.Cells(1, 1), _
.Cells(ClientCount, UBound(ClientArray, 2))) _
= ClientArray
End With

'tidy up
Set shsource=nothing
Set shdest=nothing
Set wbdest=nothing


End sub



DataCollector wrote:
Hi,

I receive a daily Excel file with data collected from a website where
clients register. The data is all contained in Column 1. There are a
variable number of clients with 16 data elements per client, and all
the HTML tags are intact.

Using VBA, I can determine the range, how many clients are represented
and I can extract the pertinent data from the HTML tags.

My problem is posting each individual client to one row in another
spreadsheet and posting the next client in the row underneath the
first, etc.

I tested the code by using a named range that represented only one
client's data using a For Each...Next loop, and posting the data to one
row on another spreadsheet.

I don't know how to go back to the raw data and pick up the next
client, post that data to the next row, and so on.

I tried using a dynamic array as:

Dim ClientArray() As Variant
Dim intClient As Integer
Dim intData As Integer
ReDim ClientArray(1 To ClientCount, 1 To 16)
For intClient = 1 To ClientCount
For intData = 1 To 16
THIS IS WHERE I DON'T KNOW WHAT TO DO
Next intData
Next intClient

Or is there a better way?

Thanks for your help.