Little problem with this code...

So you have a top and left header for each table? We can easily offset and
resize the region the macro loops through.

Sub Test2()
Dim sh As Worksheet, sh2 As Worksheet
Dim i As Long, j As Long
Dim BeginCell As Range
Dim EndCell As Range
Dim cell As Range
Set sh = Worksheets("Sheet1")
Set sh1 = Worksheets("Compiler")
j = 1
i = 1

Do While sh.Name <> sh1.Name
Set BeginCell = sh.Range(sh.Range("P3"))
Set EndCell = sh.Range(sh.Range("P4"))

With BeginCell.CurrentRegion
For Each cell In .Offset(1, 1).Resize(.Rows.Count - 1, _
.Columns.Count - 1).Cells
If (cell.Row > BeginCell.Row And cell.Row < EndCell.Row) Or _
(cell.Row = BeginCell.Row And cell.Column >= BeginCell.Column) Or _
(cell.Row = EndCell.Row And cell.Column <= EndCell.Column) Then
sh1.Cells(i, j).Value = cell.Value

If j = 8 Then
j = 1
i = i + 1
Else
j = j + 1
End If
End If
Next cell
End With

Set sh = Worksheets(sh.Range("P5").Value)
Loop
sh1.Activate
End Sub

"simonsmith" wrote:

>
> Hi JMB,
> this is pretty much perfect so thank you sooooooooooo much for doing
> this.
> To reply to your assumption, unfortunately the table has 1-8 across the
> row 1 at the top (bit number) and 0 to 39 down the A column on the left
> hand side (octet number). So the macro doesnt quite work properly when
> compiling unless I remove the octet column on each of about 100
> templates I have. What I could do is record a macro to delete that
> column at the start then add it back on at the finish!
> Would it be easier to copy everything onto a single column in the
> compiler first then transpose that it into a 8 row x 40 matrix?
>
>
> Cheers
>
> Simon
>
>
> --
> simonsmith
> ------------------------------------------------------------------------
> simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
> View this thread: http://www.excelforum.com/showthread...hreadid=543596
>
>

Hey JMB,
you are the man, but boy you must be getting sick of this!

What is happening now is when I test the compiling doesn't quite "interleave" properly. It seems to copy and position the specified cells correctly from the first sheet, but when it finds P5 and then goes to the next sheet it doesnt seem to copy exactly what is specified in that next sheet's P3 and P4. It seems to copy a few cells earlier, the required cells and a few later ones ie on the next sheet it copies cells within the matrix in additiona to what is specified in P3, P4.
I prob didnt say but for the reproduced matrix, it will be always made up of either 320 cells (or 160), there will be no gaps throughout the matrix i.e. the where P4 ends on one sheet P3 will start on the subsequent sheet so that there is continuous interleaving from one sheet to the next of the partial or whole octets of data, which in turn comprise the compiled 40 octet frame of data

Would it be easier to go to each sheet and copy the compiler to just make one 320 cell column (from 8 columns and 40 rows from parts of the various sheets) then change that column into a matrix??

Don't stress about it, I will figure something out.

I think that when I am testing that occasionally the compiler matrix does work properly when I specific certain combos of P3,P4 but will get to the bottom of this in the morning

Thanks for all your help you have been awesome

Best Regards

Simon

Is it possible you can email me an example (maybe just a few worksheets)
[email protected]

"simonsmith" wrote:

>
> Hey JMB,
> you are the man, but boy you must be getting sick of this!
>
> What is happening now is when I test the compiling doesn't quite
> "interleave" properly. It seems to copy and position the specified
> cells correctly from the first sheet, but when it finds P5 and then
> goes to the next sheet it doesnt seem to copy exactly what is specified
> in that next sheet's P3 and P4. It seems to copy a few cells earlier,
> the required cells and a few later ones ie on the next sheet it copies
> cells within the matrix in additiona to what is specified in P3, P4.
> I prob didnt say but for the reproduced matrix, it will be always made
> up of either 320 cells (or 160), there will be no gaps throughout the
> matrix i.e. the where P4 ends on one sheet P3 will start on the
> subsequent sheet so that there is continuous interleaving from one
> sheet to the next of the partial or whole octets of data, which in turn
> comprise the compiled 40 octet frame of data
>
> Would it be easier to go to each sheet and copy the compiler to just
> make one 320 cell column (from 8 columns and 40 rows from parts of the
> various sheets) then change that column into a matrix??
>
> Don't stress about it, I will figure something out.
>
> I think that when I am testing that occasionally the compiler matrix
> does work properly when I specific certain combos of P3,P4 but will get
> to the bottom of this in the morning
>
> Thanks for all your help you have been awesome
>
> Best Regards
>
> Simon
>
>
> --
> simonsmith
> ------------------------------------------------------------------------
> simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
> View this thread: http://www.excelforum.com/showthread...hreadid=543596
>
>