此方法利用循环和数组来传输数据。这不是最有活力的方法，但它完成了工作。所有循环都使用现有的常量，所以如果您的数据集更改，您可以调整常量，它应该运行很好。确保调整工作表名称以匹配您在Excel文档中使用的名称。实际上，这样做是将您的数据加载到数组中并将其转换到另一个工作表上。

如果您的数据集大小变化不大，您将需要包括一些逻辑来调整循环变量和数组大小声明。如果是这样的话，让我知道，我会找出如何做到这一点，并发布一个编辑。

  Sub moveTimeData （）

设置source = ThisWorkbook.Sheets（RawData）
设置dest = ThisWorkbook.Sheets（TransposeSheet）

 Const dataSetSize = 15 

 Const row15Start = 3 
 Const row15End = 18 
 Const row30Start = 21 
 Const row30End = 36 

 Const colStart = 2 

 Const destColStart = 2 
 Const dest15RowStart = 2 
 Const dest30RowStart = 3 

 Dim time15Array（）As Integer 
 Dim time30Array（）As Integer 
 ReDim time15Array（0到dataSetSize）
 ReDim time30Array（0到dataSetSize）

 Dim X As Integer 
 Dim Y As Integer 
 Dim c As Integer 
c = 0 

对于X = row15Start到row15End 
 time15Array（c）= source.Cells（X，colStart）.Value 
c = c + 1 
下一步X 

c = 0 
对于X = row30Start到row30End 
 time30Array（c）= source.Cells（X，colStart）.Value 
 c = c + 1 
下一个X 

对于X = 0对于dataSetSize 
 dest.Cells（dest15RowStart，X + destColStart）.Value = time15Array（X）
下一个X 

对于X = 0到dataSetSize 
 dest.Cells（dest30RowStart，X + destColStart）.Value = time30Array（X）
下一个X 

 End Sub 
 

EDIT->我认为这是你在阅读之后寻找的编辑

  Sub moveTimeData（）

设置source = ThisWorkbook.Sheets（RawData ）
设置dest = ThisWorkbook.Sheets（TransposeSheet）

 Const numberDataGroups = 4 
 Const dataSetSize = 15 
 Const stepSize = 18 

 Const sourceRowStart = 3 

 Const sourceColStart = 2 

 Const destColStart = 2 
 Const destRowStart = 2 



 Dim X As Integer 
 Dim Y As Integer 
 Dim currentRow As Integer 
 currentRow = destRowStart 



对于X = 0到numberDataGroups 
对于Y = 0到dataSe tSize 
 dest.Cells（currentRow，Y + destColStart）.Value = source.Cells（（X * stepSize）+（Y + sourceRowStart），sourceColStart）
下一个Y 
 currentRow = currentRow + 1 
下一个X 


 End Sub 
 

现在这个工作的关键是知道数据转储后你处理的数据组有多少。您需要包含用于检测的逻辑或调整名为numberDataGroups的常量来反映您拥有的组数。注意：我使用类似的技术来遍历数据，其数据以Row Major格式存储。

I have a little experience with VBA, and I would really appreciate any help with this issue. In a basic sense, I need to convert 2 columns of data in sheet 1 to rows of data in sheet 2.

It currently looks like this in Excel:

And I need it to look like this:

I've already written the code to transfer the headings over to sheet 2, and it works fine. I'm just having issues with transferring the actual values in the correct format. Right now, the body of my code is

ws.Range("B3").Copy
ws2.Range("C2").PasteSpecial xlPasteValues

ws.Range("B4").Copy
ws2.Range("D2").PasteSpecial xlPasteValues

ws.Range("B5").Copy
ws2.Range("E2").PasteSpecial xlPasteValues

ws.Range("B6").Copy
ws2.Range("F2").PasteSpecial xlPasteValues

continued on and on. However, this really won't work, as the actual document I'm working on has tens of thousands of data points. I know there's a way to automate this process, but everything I've tried has either done nothing or given an error 1004.

Any help with this would be greatly appreciated!!

Edit: There are hundreds of little sections of data, each 18 rows long (1 row for the frame #, 1 row for the time, and 1 row for each of the 16 channels). I'm trying to get it into a loop with a step size of 18. Is that possible? I'm fine with loops, but I've never done a loop with copying and pasting cell values

This method leverages loops and arrays to transfer the data. It isn't the most dynamic method but it gets the job done. All the loops use existing constants, so if your data set changes you can adjust the constants and it should run just fine. Make sure to adjust the worksheet names to match the names you are using in your excel document. In effect, what this is doing is loading your data into an array and transposing it onto another worksheet.

If your data set sizes change quite a bit, you will want to include some logic to adjust the loop variables and array size declarations. If this is the case, let me know and I'll figure out how to do that and post an edit.

Sub moveTimeData()

Set source = ThisWorkbook.Sheets("RawData")
Set dest = ThisWorkbook.Sheets("TransposeSheet")

Const dataSetSize = 15

Const row15Start = 3
Const row15End = 18
Const row30Start = 21
Const row30End = 36

Const colStart = 2

Const destColStart = 2
Const dest15RowStart = 2
Const dest30RowStart = 3

Dim time15Array() As Integer
Dim time30Array() As Integer
ReDim time15Array(0 To dataSetSize)
ReDim time30Array(0 To dataSetSize)

Dim X As Integer
Dim Y As Integer
Dim c As Integer
c = 0

For X = row15Start To row15End
    time15Array(c) = source.Cells(X, colStart).Value
    c = c + 1
Next X

c = 0
For X = row30Start To row30End
    time30Array(c) = source.Cells(X, colStart).Value
    c = c + 1
Next X

For X = 0 To dataSetSize
    dest.Cells(dest15RowStart, X + destColStart).Value = time15Array(X)
Next X

For X = 0 To dataSetSize
    dest.Cells(dest30RowStart, X + destColStart).Value = time30Array(X)
Next X

End Sub

EDIT-> I think this is what you are looking for after reading your edits

Sub moveTimeData()

Set source = ThisWorkbook.Sheets("RawData")
Set dest = ThisWorkbook.Sheets("TransposeSheet")

Const numberDataGroups = 4
Const dataSetSize = 15
Const stepSize = 18

Const sourceRowStart = 3

Const sourceColStart = 2

Const destColStart = 2
Const destRowStart = 2



Dim X As Integer
Dim Y As Integer
Dim currentRow As Integer
currentRow = destRowStart



For X = 0 To numberDataGroups
    For Y = 0 To dataSetSize
        dest.Cells(currentRow, Y + destColStart).Value = source.Cells((X * stepSize) + (Y    + sourceRowStart), sourceColStart)
    Next Y
    currentRow = currentRow + 1
Next X


End Sub

Now the key to this working is knowing how many groups of data you are dealing with after the data dump. You either need to include logic for detecting that or adjust the constant called numberDataGroups to reflect how many groups you have. Note: I leveraged a similar technique for traversing arrays that have their data stored in Row Major format.