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.

[TABLE="width: 0"]
<tbody>[TR]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]QUESTION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SERVICE[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]VARNAME[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]QUESTION_TEXT[/TD]
[TD]Speed of admission process[/TD]
[/TR]
[TR]
[TD]SECTION[/TD]
[TD]Admission[/TD]
[/TR]
[TR]
[TD]STANDARD[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]SCREENING[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]TOP_BOX_SCALE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOP_BOX_ANSWER[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QUESTION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SERVICE[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]VARNAME[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]QUESTION_TEXT[/TD]
[TD]Courtesy of the person who admitted you[/TD]
[/TR]
[TR]
[TD]SECTION[/TD]
[TD]Admission[/TD]
[/TR]
[TR]
[TD]STANDARD[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]SCREENING[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]TOP_BOX_SCALE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOP_BOX_ANSWER[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QUESTION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SERVICE[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]VARNAME[/TD]
[TD]AGE[/TD]
[/TR]
[TR]
[TD]QUESTION_TEXT[/TD]
[TD]Patient's age[/TD]
[/TR]
[TR]
[TD]SECTION[/TD]
[TD]Background[/TD]
[/TR]
[TR]
[TD]STANDARD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SCREENING[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOP_BOX_SCALE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOP_BOX_ANSWER[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is what I need it to look like:
[TABLE="width: 0"]
<tbody>[TR]
[TD]QUESTION[/TD]
[TD]SERVICE[/TD]
[TD]VARNAME[/TD]
[TD]QUESTION_TEXT[/TD]
[TD]SECTION[/TD]
[TD]STANDARD[/TD]
[TD]SCREENING[/TD]
[TD]TOP_BOX_SCALE[/TD]
[TD]TOP_BOX_ANSWER[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IN[/TD]
[TD]A1[/TD]
[TD]Speed of admission process[/TD]
[TD]Admission[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QUESTION[/TD]
[TD]SERVICE[/TD]
[TD]VARNAME[/TD]
[TD]QUESTION_TEXT[/TD]
[TD]SECTION[/TD]
[TD]STANDARD[/TD]
[TD]SCREENING[/TD]
[TD]TOP_BOX_SCALE[/TD]
[TD]TOP_BOX_ANSWER[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IN[/TD]
[TD]A2[/TD]
[TD]Courtesy of the person who admitted you[/TD]
[TD]Admission[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QUESTION[/TD]
[TD]SERVICE[/TD]
[TD]VARNAME[/TD]
[TD]QUESTION_TEXT[/TD]
[TD]SECTION[/TD]
[TD]STANDARD[/TD]
[TD]SCREENING[/TD]
[TD]TOP_BOX_SCALE[/TD]
[TD]TOP_BOX_ANSWER[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IN[/TD]
[TD]AGE[/TD]
[TD]Patient's age[/TD]
[TD]Background[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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