Output to Table, Dynamic Dataset - Specific format } Question

[laxcat73]

I have a dataset that is 2 columns. The data is dynamic and updated daily.

<TABLE style="WIDTH: 155pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=206 border=0><COLGROUP><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=128 height=17>LEV</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>MDB</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>MND</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>RGB</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">18</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SC5</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">18</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SKS</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SL2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">---</TD></TR></TBODY></TABLE>

I was wondering is there is a way to output in this format:

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>LEV</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>3</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>MDB</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>8</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>MND</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>RGB</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">18</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SC5</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">18</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SKS</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SL2</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">---</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

So, simply, I want it to turn a pair of columns into 3 pairs of columns that reads as a paragraph would in that it continues across the row to 6 columns then goes to the next row and repeats.

Any help is much appreciated.

 

[laxcat73]

What is the named range exactly?

Does it cover a particular section of the worksheet?

Does it identify a cell?

.

The named range is named 'ED'

It does cover a particular section of the worksheet and is static. Other named ranges also occupy that same sheet.

It is not identifying any cells, it gives an error and debugs.

 

[Norie]

Perhaps I didn't put that quite right, what's the range the named range refers to?

That's what I meant by 'identify'.

The code I posted is based on the data being in columns A & B and starting in row 1.

So we start in top left corner and work down.

You might be able to use Range("ED").Cells(1,1) which is the top left corner of the named range.

 

[laxcat73]

Perhaps I didn't put that quite right, what's the range the named range refers to?

That's what I meant by 'identify'.

The code I posted is based on the data being in columns A & B and starting in row 1.

So we start in top left corner and work down.

You might be able to use Range("ED").Cells(1,1) which is the top left corner of the named range.

Thank you for the advice. I will try this and see if it works.

 

[laxcat73]

Run-time error '1004':

Application-defined or oject-defined error

It highlights the following

Set rngSrc = Worksheets("TOTALS").Range("ED").Cells(1, 1)

 

[Norie]

This is because you used the name ED, which is column 134 or thereabouts.

In VBA 'ED' is being interpreted as a reference to a range, and it's missing some bits.

eg a row number

You could try this:

Set rngSrc = Range(ThisWorkbook.Names("ED").RefersTo).Cells(1, 1)

PS I know you can use it on a worksheet.

 

[laxcat73]

This is because you used the name ED, which is column 134 or thereabouts.

In VBA 'ED' is being interpreted as a reference to a range, and it's missing some bits.

eg a row number

You could try this:

Set rngSrc = Range(ThisWorkbook.Names("ED").RefersTo).Cells(1, 1)

PS I know you can use it on a worksheet.

The line you gave me above is coming up with the same error.

Sorry, named range "ED" is currently rows 94 to 184 in columns A and B of the main sheet rather than column 'ED'. The columns A and B are broken into different categories, each a named range which is dynamic in the row numbers.

I have to leave work now but I really appreciate your help.

 

[laxcat73]

Also, I changed the named range to "DepED" from "ED" to avoid issues with VBA looking at column 'ED'


Now, it works, but it giving me a string across row 1, same as before.

Here is my entire code:

Sub ED()
Dim rngSrc
Dim rngDst
    Set rngSrc = Range(ThisWorkbook.Names("DepED").RefersTo).Cells(2, 1)
    Set rngDst = Worksheets("ED").Range("A1")
    While rngSrc.Value <> ""
        rngSrc.Resize(, 2).Copy rngDst
        If rngDst.Column = 8 Then
            Set rngDst = rngDst.Offset(1, -4)
        Else
            Set rngDst = rngDst.Offset(, 2)
        End If
        Set rngSrc = rngSrc.Offset(1)
    Wend
    Sheets("ED").Select
    Range("A1").Select
End Sub

 

[Norie]

I know it's not column ED.

VBA thinks you mean column ED when you use Range("ED"), which is the problem.

Why do you want to use this range anyway?

Can't you just change A1 to the cell reference of the top-left corner of the range the data is in.

 

[Norie]

This might work.

Sub MoveThingsAboutABit()
Dim rngSrc As Range
Dim rngDst As Range
Dim I As Long
Dim X As Long
    Set rngDst = Worksheets("ED").Range("A1")
    
    X = 3
    
    For Each rngSrc In Range("DepED").Columns(1).Cells
    
        rngSrc.Resize(, 2).Copy rngDst
        I = I + 1
        
        If I > X Then
        
            Set rngDst = rngDst.Offset(1, -(2 * X))
            I = 0
            
        Else
            Set rngDst = rngDst.Offset(, 2)
        End If
        
        Set rngSrc = rngSrc.Offset(1)
 
    Next rngSrc
    
End Sub

 

[laxcat73]

I know it's not column ED.

VBA thinks you mean column ED when you use Range("ED"), which is the problem.

Why do you want to use this range anyway?

Can't you just change A1 to the cell reference of the top-left corner of the range the data is in.

I could, but the datasets are dynamic. It's employees, and we add them and delete them based on employement status so that cell reference will not be static. I'm about to try the next bit you posted. I went out last night and got a MrExcel VBA book too to help learn some of this