vbscript to move data to new worksheet

[KerryT]

Hi

I want to transform some data into a table on a new sheet but to me it seems complicated so I am not sure if it is possible. Below is the source data format

[TABLE="width: 1322"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/01/2014[/TD]
[TD] 01/02/2014 [/TD]
[TD]01/03/2014[/TD]
[TD] 01/04/2014 [/TD]
[TD]01/05/2014 [/TD]
[TD]01/06/2014 [/TD]
[TD]01/07/2014 [/TD]
[TD]01/08/2014 [/TD]
[TD]01/09/2014 [/TD]
[TD]01/10/2014 [/TD]
[TD]01/11/2014 [/TD]
[TD]01/12/2014[/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Jan-14[/TD]
[TD]Feb-14[/TD]
[TD]Mar-14[/TD]
[TD]Apr-14[/TD]
[TD]May-14[/TD]
[TD]Jun-14[/TD]
[TD]Jul-14[/TD]
[TD]Aug-14[/TD]
[TD]Sep-14[/TD]
[TD]Oct-14[/TD]
[TD]Nov-14[/TD]
[TD]Dec-14[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]50%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]£1,000[/TD]
[TD]£1,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]150%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD][/TD]
[TD][/TD]
[TD]£1,000[/TD]
[TD]£1,000[/TD]
[TD]£1,000[/TD]
[TD]£1,000[/TD]
[TD]£1,000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I want the script to work down column A and for every entry go along the row and transform the data into the following format.

[TABLE="width: 525"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Value[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]50%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]01/01/2014[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]50%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]01/02/2014[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]50%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]01/03/2014[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]150%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]01/03/2014[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]150%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]01/04/2014[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]150%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]01/05/2014[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]150%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]01/06/2014[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]150%[/TD]
[TD] [/TD]
[TD]DM[/TD]
[TD]£1,000[/TD]
[TD]01/07/2014
[/TD]
[/TR]
</tbody>[/TABLE]


The number of rows in the new column should = the number of values there are against Dates. The first 4 columns are copied across and then repeated for each date that has a value. For each customer there can be up to 12 rows for the 12 months of the year.

Can anyone help, thanks.

Kerry

 

[Alan_P]

Hi Kerry,

Give this code a go....

you'll need to create a new sheet called "Transformed" (can be renamed later)

Assuming the first customer name is in Cell A3, Dates are across row 1 starting at column E... this should work;

Sub Transformed()


Dim UsdCols As Long
Dim UsdRws As Long
Dim i As Long
Dim n As Long




UsdCols = Cells(1, Columns.Count).End(xlToLeft).Column
UsdRws = Range("A" & Rows.Count).End(xlUp).Row


    For i = 3 To UsdRws
    
        For n = 5 To UsdCols
        
            If Cells(i, n).Value <> "" Then
            Range("a" & i, "d" & i).Copy Sheets("Transformed").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Cells(i, n).Copy Sheets("Transformed").Range("E" & Rows.Count).End(xlUp).Offset(1)
            Cells(1, n).Copy Sheets("Transformed").Range("F" & Rows.Count).End(xlUp).Offset(1)
            End If
            
        Next n
        
    Next i
    
Sheets("Transformed").Activate


End Sub

Cheers,
Alan.

 

[KerryT]

Hi Alan

Thanks, I initially had a subscript out of range error which I managed to resolve myself by calling a worksheet Transformed.

I have run it and I get the information in the correct columns which is brilliant so thank you. It also proves that it can be done. Ultimately I need this to run on a much larger spreadsheet and operate on sets of cells. It also ideally needs the column headings.

The set of columns will always be the same as there is one for each month. The set of rows will vary as the number of customers changes week by week.

Is there any simple way of specifying the cells that it operates on? If I need it to work on multiple sections can you repeat the loop and how would it know to pend to end of the list on the transform sheet.

I have this and few other things that I need help with so I am interested to understand if you can help.

Thanks