CODEWITHSUNDEEP
vbaexceltranspose
Adryan Permana
Adryan Permana

Reputation: 127

Excel Macro copy and transpose data to other workbook with special condition

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.

Upvotes: 1

Views: 233

Answers (1)

A.S.H
A.S.H

Reputation: 29352

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

Upvotes: 1

Related Questions