CopyPasteTransform Macro to new sheet Macro Question

[MrsMSalt91]

New to VBA, using Excel 2013. I am trying to select a rage columns and rows to copy and paste and transpose into a new sheet. I have searched for anything close and have not been successful. I am including my raw data then a generic walkthrough of what I need the Macro to do. My issue is, I need to create a variable for the ranges because after the first selected copy and paste which would be A2:B10, then paste transposed to Sheet2 A1. The macro needs to then select A11:B10 (always 9 rows) select those then paste transformed to Sheet2 A3. Please let me know if I am totally confusing you all.

QUESTION SERVICE IN VARNAME A1 QUESTION_TEXT Speed of admission process SECTION Admission STANDARD Y SCREENING N TOP_BOX_SCALE TOP_BOX_ANSWER QUESTION SERVICE IN VARNAME A2 QUESTION_TEXT Courtesy of the person who admitted you SECTION Admission STANDARD Y SCREENING N TOP_BOX_SCALE TOP_BOX_ANSWER QUESTION SERVICE IN VARNAME AGE QUESTION_TEXT Patient's age SECTION Background STANDARD SCREENING TOP_BOX_SCALE TOP_BOX_ANSWER <tbody> </tbody>

<tbody>
</tbody>

This is what I need it to look like:

QUESTION	SERVICE	VARNAME	QUESTION_TEXT	SECTION	STANDARD	SCREENING	TOP_BOX_SCALE	TOP_BOX_ANSWER
	IN	A1	Speed of admission process	Admission	Y	N
QUESTION	SERVICE	VARNAME	QUESTION_TEXT	SECTION	STANDARD	SCREENING	TOP_BOX_SCALE	TOP_BOX_ANSWER
	IN	A2	Courtesy of the person who admitted you	Admission	Y	N
QUESTION	SERVICE	VARNAME	QUESTION_TEXT	SECTION	STANDARD	SCREENING	TOP_BOX_SCALE	TOP_BOX_ANSWER
	IN	AGE	Patient's age	Background

<tbody>
</tbody>

Here is the VBA Macro I created by just stepping through the process:

Sub CopyPasteTransform()
'
' CopyPasteTransform Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Range("A2:B10").Select
Selection.Copy
Sheets("Transformed").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Main").Select
Range("A11:B19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transformed").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Main").Select
Range("A20:B28").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transformed").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub


Any help you can offer I would be GREATLY appreciative!
Thanks,
Marianne

 

[My Aswer Is This]

Try this:

Sub Copy_Transform()
'Modified  11/8/2018  2:14:35 AM  EST
Application.ScreenUpdating = False
Sheets("Main").Activate
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As Long
ans = 1
    For i = 1 To Lastrow Step 9
        Sheets("Main").Cells(i, 1).Resize(9, 2).Copy
        Sheets("Transformed").Cells(ans, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        ans = ans + 2
    Next
    Application.CutCopyMode = Talse
Application.ScreenUpdating = True
End Sub