1

I am still new to macro. I have data in first workbook with 300 rows of data in one column.I need to transpose every 6 rows of data to 6 columns in other workbook. For example data from A1:A6 must be transposed to A1:F1. Again data from A7:A14 must be transposed to A2:F2.

I have this code. The data is saved on vDB in array

Dim vDB
    vDB = rsData.getRows
         TargetRange.Cells(1, 1).Resize(UBound(vDB, 1) + 1, UBound(vDB, 2) + 1) = vDB

With this code, I am able to copy and transpose data to other workbook. But it only works from one column to one row. I cannot find the way to do for every 6 rows to 6 column. Is there any way to transpose data for every 6 rows to 6 column with my code above? I appreciate for the help.

A.S.H
  • 29,101
  • 5
  • 23
  • 50
Adryan Permana
  • 127
  • 1
  • 1
  • 10

1 Answers1

1

No need for GetRows. After getting your RecordSet rsData, use this code: 

Dim i As Long
Do Until rsData.EOF
  targetRange.Cells(1 + Int(i / 6), 1 + i Mod 6).value = rsData.Fields(0).value
  i = i + 1
  rsData.MoveNext
Loop
A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • it not transpose the data. The data need to be transposed. – Adryan Permana Jul 11 '17 at 04:26
  • @AdryanPermana *"A1:A6 must be transposed to A1:F1. Again data from A7:A14 must be transposed to A2:F2"* the suggested code is to put the data directly from the recordset `rsData` in the way you described. It supposes that your recordset has one field (column) and instead of putting the values in one column, it puts them in `A1:F1` then `A2:F2` and so on. – A.S.H Jul 11 '17 at 05:19
  • sorry my mistake. I did something wrong. It works. Thanks – Adryan Permana Jul 11 '17 at 06:32