Insert rows and copying values if cells contain values

Hi

Add a new sheet to the workbook, called sheet2 then try

Sub aaa()
  Dim OutSh As Worksheet
  Set OutSh = Sheets("Sheet2")
  OutSh.Range("A1:F1").Value = Array("ID", "Measure", "Choice", "Importance", "Fixed", "Bpost")
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To 32 Step 2
      If Len(Cells(i, j)) > 0 Then
        outrow = OutSh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        OutSh.Cells(outrow, 1).Value = Cells(i, 1).Value
        OutSh.Cells(outrow, 2).Value = Cells(1, j).Value
        OutSh.Cells(outrow, 3).Value = Cells(i, j).Value
        OutSh.Cells(outrow, 4).Value = Cells(i, j + 1).Value
        OutSh.Cells(outrow, 5).Value = Cells(i, "AH").Value
        OutSh.Cells(outrow, 6).Value = Cells(i, "AI").Value
      End If
    Next j
  Next i
End Sub

HTH

rylo

Thanks rylo!

The test worked like a charm. However, I only copied a sample out of the dataset. In fact, I've got over 30 "fixed" variables that I need to copy. How do I add them in the script? I presume that "OutSh.Cells()" should do the trick, one for every variable. However, what values do I need to use then?

Thanks a million!

Hi

Well you could just continue with the structure of the

outsh.cells(outrow,?).value = cells(i,"??").value

where ? is the column that gets the fixed variable and "??" is the column that contains it.

However, if you have a block of fixed variables that are in contiguous cells, then you may be able to transfer / copy a block of cells more easily. Can you give a more detailed example of your entire structure and what has to be copied.

rylo

Hi rylo,

I've attached the full document. All values are in sheet "TEST". However the variable names have changed a bit. Everything, except the following, are "fixed" so to say:
maat01a & belang01a again belong together
and these go up to maat01p & belang01p (T1:AY1). In the previous example I gave them a simpler name.

The structure of the output is in the other sheet "Uitkomst". The fixed output variables are cells A1 and E1:AR1; the variable output variables are cells B1,C1,D1.

I hope you still get it but the previous macro worked fabulous!

Greetings

Hi

Can you update your example file with an expected output from the sample data. I can find most of the items, but don't have the foggiest where the data for B1, C1 and D1 is supposed to come from, or where the output from T to AY is supposed to go on the output sheet.

rylo

I updated the file with the first ID, number 908.

Hi

Think I've matched them all.

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Uitkomst")
  
  Sheets("TEST").Activate
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    For j = 20 To 50 Step 2
      If Len(Cells(i, j)) > 0 Then
        outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        OutSH.Cells(outrow, 1).Value = Cells(i, 1).Value
        OutSH.Cells(outrow, 2).Value = Right(Cells(1, j).Value, 1)
        OutSH.Cells(outrow, 3).Value = Cells(i, j).Value
        OutSH.Cells(outrow, 4).Value = Cells(i, j + 1).Value
        OutSH.Cells(outrow, 5).Resize(1, 18).Value = Cells(i, 2).Resize(1, 18).Value
        OutSH.Cells(outrow, 23).Resize(1, 22).Value = Cells(i, 52).Resize(1, 22).Value
      End If
    Next j
  Next i
  
End Sub

If this doesn't match, then give the cell references of where the data in Uitkomst comes from in TEST.

rylo

Yes indeed, I calculated the number of cells containing a value and it equals 1690. Great, thanks! This saved me a lot of time and moreover a smaller chance to make errors ;-)