|I don't know that there is a more efficient way than Copy/Paste. That method is certainly more efficient than looping through each cell and setting one cell equal to another one.|
The only other option I can think of is to use a macro to place formula's in the cells on Sheet2 which refer back to the cells on Sheet1. If the layout of the data was consistant, that macro could be run once to populate Sheet2 with the formulas and then the formulas would do their own thing from that point on.
However, I did add some code to my original macro that might help. In this version I turned off ScreenUpdataing so that you won't see the Copy/Paste operation. Everything will be done in the background and VBA will return control to the user once all of the data has been transposed. That will be a bit more efficient than watching the "large amounts of data" being transposed to Sheet2.
'Turn off Screen Updating
Application.ScreenUpdating = False
'Determine last Source Row on Sheet1
last_srcRw = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Initialize Destination Row variable
dstRw = 1
'Loop through Source Rows
For srcRw = 1 To last_srcRw
Sheets("Sheet1").Range("A" & srcRw & ":H" & srcRw).Copy
'PasteSpecial Transposed to Sheet2 Column A
Sheets("Sheet2").Range("A" & dstRw).PasteSpecial Transpose:=True
'Increment Destination Row by 8
dstRw = dstRw + 8
'Turn On Screen Updating
Application.ScreenUpdating = = True
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.