0

I wish to copy cells from worksheet 1 to worksheet 2 that are originally in 2 columns ( x,y) and to past them into worksheet 2 so it has the following format regardless of the total number of data in the x,y columns: 

x   y      //////////////////       into                    
11  12  ////////////////////     11 12  21  22  31  32

21  22  ////////////////////         41 42  51  52  61  62

31  32  ////////////////////         ...    ...             
41  42                          
51  52                          
61  62                          
…   …               

Sub CommandButton3_Click()
    Dim ar As Integer
    Dim al As Integer
    Dim asp As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    ar = Range("A" & Rows.Count).End(xlUp).Row
    al = ar - 6

    asp = Round(al / 3)
    i = 7 '(row number of first cell to copy)
    j = 5  '(row number of pasted cell)
    k = 2   '(column number of pasted cell)

    For i = i To ar
        For j = j To asp
            Worksheets("XSections").Activate
            Worksheets("XSections").Range(Cells(i, 1), Cells(i, 2), Cells(i +     1, 1), Cells(i + 1, 2), Cells(i + 2, 1), Cells(i + 2, 2)).Copy
            Worksheets("Sorted").Activate
            Worksheets("Sorted").Range(Cells(j, k), Cells(j, k + 1), Cells(j, k + 2), Cells(j, k + 3), Cells(j, k + 4), Cells(j, k + 5), Cells(j, k + 6)).Select
            ActiveSheet.Paste

            i = i + 3
        Next j
    Next i
End Sub

As soon as i insert a for function, things get complicated and there is no output.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
JRL
  • 1
  • 1
    It is a little confusing what you are wanting for output. I think you can help by adding more comments in your code. For example, why do you skip 3 i and then use next i? A couple of places to start looking: 1)`Cells` may work better as `.Cells`; 2) `Activate` and `ActiveSheet` are often contributors to unexpected behavior. You may have better luck setting sheets to variables to ensure that you are getting the behavior that you expect. – jessi Jul 05 '19 at 17:47
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • And note that Excel has more rows than fit into `Integer`. Therefore all your variables should be declared as `Long`. – Pᴇʜ Jul 08 '19 at 06:32

0 Answers0