Search code examples
excelvbacopy-paste

VBA Excel copy-paste from workbook to another workbook transpose values and different ranges

The input data are always columns in 7 different sheets that need to be output in different columns across different non-in-sequence rows

Q2_1    Q3_1    Q4_1
13      17      11
4        2       5
3        2       4
2        2       4
6        5       4

The sequence of the rows (output) is 5, 10, 15, 23, 28, 33, 38, 43, 48, 53, 61, 66, 71, 79, 84, 89, 94, 102, 107, 112, 117, 122, 128, 135, 140, 148, 153, 158, 166, 171, 179, 184, 189, 194

And the Columns are from i5 to M5 for Q2_1, i10 to M10 for Q3

I have tried 2 fors and 2 do loops, with no luck. Is there any way to use arrays?

I am very to programming.

Sub CPRelative()

    Dim n As Integer
    Dim i As Integer
    Dim itotal As Integer

    Windows("book1.xlsx").Activate
    Sheets(3).Select

    For n = 2 To 35
        ActiveSheet.Range(Cells(4, n), Cells(8, n)).Select
        Selection.Copy

        Windows("book2.xlsm").Activate


         For i = 5 To 194

            Select Case i
            Case 5, 10, 15, 23, 28, 33, 38, 43, 48, 53, 61, 66, 71, 79, 84, 89, 94, 102, 107, 112, 117, 122, 128, 135, 140, 148, 153, 158, 166, 171, 179, 184, 189, 194

                ActiveSheet.Range(Cells(i, 9), Cells(i, 13)).Select
               Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            End Select
        Next n
End Sub

And do until and do while

Sub NestedLoop()
    Dim n As Integer
    Dim i As Integer

    Windows("book1.xlsx").Activate
    Sheets(3).Select

    n = 2
    Do Until ActiveSheet.Range(Cells(4, n), Cells(8, n)).Value = ("8,n")

        ActiveSheet.Range(Cells(4, n), Cells(8, n)).Select
        Selection.Copy

        Windows("book2.xlsm").Activate
        Sheets(1).Select

        'i = 5

        Do While ActiveSheet.Range(Cells(i, 9), Cells(i, 13)).Value = ""
            Select Case i
            Case 5, 10, 15, 23, 28, 33, 38, 43, 48, 53, 61, 66, 71, 79, 84, 89, 94, 102, 107, 112, 117, 122, 128, 135, 140, 148, 153, 158, 166, 171, 179, 184, 189, 194
               ActiveSheet.Range(Cells(i, 9), Cells(i, 13)).Select
               Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
           End Select

           i = i + 1
       Loop
       n = n + 1
    Loop
End Sub
Solution

  • Adding before and after screenshots will lead to quicker responses and better answers.

    Here is a video from my favorite VBA Excel video series that you must watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)

    Source

    enter image description here

    Target

    enter image description here

    Sub CPRelative2()
    Dim n As Long, r As Long
    Dim arrRows As Variant
    Dim Source As Range, Target As Range
    Set Source = Workbooks("180610_SequencingScenarioTEST1.xlsx").Worksheets("Sheet1").Cells
    Set Target = Workbooks("180610_TestSurveyAnalysisTest1.xlsm").Worksheets("Sheet1").Cells

    arrRows = Array(5, 10, 15, 23, 28, 33, 38, 43, 48, 53, 61, 66, 71, 79, 84, 89, 94, 102, 107, 112, 117, 122, 128, 135, 140, 148, 153, 158, 166, 171, 179, 184, 189, 194)
    For n = 2 To 35
         r = arrRows(n - 2)
        Target.Cells(r, "I").Resize(1, 5).Value = WorksheetFunction.Transpose(Source.Cells(4, n).Resize(5, 1).Value)
    Next

End Sub