VBA Copy and Paste to new Worksheet

Question

The following code basically copies and pastes from Sheet1 to Sheet2 in the same order it was in on Sheet2. I need it to paste the values into the same column on Sheet2 so I can run a sort for all of the values. I can't seem to figure out how to get it to Paste everything to the same column on Sheet2. Thanks.

Sub InfoSharing()

Dim lastrowDB As Long, lastrow As Long
Dim array1, array2, i As Integer

With Sheets("Sheet1")
    lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

array1 = Array("C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N")
array2 = Array("C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N")

For i = LBound(array1) To UBound(array1)
    With Sheets("Sheet1")
        lastrow = Application.Max(3, .Cells(.Rows.Count, array1(i)).End(xlUp).Row)
        .Range(.Cells(3, array1(i)), .Cells(lastrow, array1(i))).Copy
        Sheets("Sheet2").Range(array2(i) & lastrowDB).PasteSpecial xlPasteValues
    End With

Next
Application.CutCopyMode = False

End Sub

Where do you want to paste data? Do you have excact location? — lowak
it can go anywhere i would like for it to be pasted to A1 so all the values in Sheet2 are in column A. — user3846393
Well... your code now copies data and pastes in the same column. So I don't really know what is your problem. — lowak
It counts values from Column A, depending on how many values are there it copies data from Sheet1 range(C3:N3) to row (which depends on that first counting) to Sheet2. — lowak
I need all values in range (C3:N3) to be pasted into column A on Sheet2. So like Sheet1(C3:C17) to Sheet2(A1:A15) then Sheet1(D3:D17) to Sheet2(A16:A30) and so on. — user3846393

lowak lowak · Accepted Answer · 2014-08-04T06:36:29

Your task is easy to do, just look below:

Dim i As Byte, y As Byte, x As Integer

For i = 3 To 5 'i is a number of column counts from left to right in Sheet1 to be copied

    For y = 3 To 17 'y is a number of row in Sheet1 to be copied

        x = x + 1 'x is a number of row in Sheet2 to be pasted

        Sheets("Sheet2").Cells(x, 1).Value = Sheets("Sheet1").Cells(y, i).Value

    Next y

Next i

I made a nested loop for your coping problem. It goes as many columns as you need (adjust i variable), through as many rows you need (adjust y variable) and pasting everything to A columns in Sheet2 (one by one, using a variable as a row number.

If you need something more, do not hesitate to ask in the comment.

VBA Copy and Paste to new Worksheet

2 Answers