Excel macro FOR EACH loop to copy paste same range in different worksheets and paste to next column

0
votes

I have several worksheets (Worksheet A, Worksheet B, Worksheet C) in a workbook. I want to copy the same range from each of these worksheets and paste them to another workbook's worksheet, each one going in the next column. I know how to write the code to copy, but the harder part is the pasting. This is what I'm thinking:

sub copypaste ()

dim wb as workbook 'this workbook
dim destination as workbook 'destination workbook
dim ws as worksheet
dim ws1 as worksheet '(in this workbook)
dim ws2 as worksheet '(in this workbook)
dim ws3 as worksheet '(in this workbook)
dim DestWorksheet as worksheet 

For Each ws in wb.worksheets  (referring to ws1, ws2, and ws3)
Range.("D1:D231").Copy

DestWorksheet.(I WANT TO PASTE DATA FROM WS1 in Column A in the destination worksheet, Ws2 in Column B, Ws3 in Column3) etc.
2
excelvba

2 Answers

0
votes

Try the following. This code uses indexes for both the worksheet and column at the same time, so at the nth sheet iteration values will also be pasted to the nth column.

Bear in mind that I have dropped .Copy. This function is not necessary if you are only copying values and for that use my code is faster. If you do need to copy more than merely values, replace the .Range(.Cells(1, i), .Cells(231, ... line to a copy paste line.

Sub copypasta()

Dim i As Integer
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet

For i = 1 To wb.Sheets.Count
    With Workbooks(DESTINATION).Sheets(DESTINATION)
        .Range(.Cells(1, i), .Cells(231, i)).Value = wb.Sheets(i).Range("D1:D231").Value
    End With
Next i
End Sub
0
votes

Don't use copy and paste, that is inefficient

Just put the value of an range in another, in the location you want 

DestWorksheet.Range("A1:A231").Value2 = Range("D1:D231").Value2
ws2.Range("B1:B231").Value2 = Range("D1:D231").Value2
ws3.Range("C1:C231").value2 = Range("D1:D231").Value2