CODEWITHSUNDEEP
excelvba
user13135566
user13135566

Reputation: 17

Copying data from multiple sheets into 1 sheet

I am trying to copy specific cells (I106 to I160) from multiple sheets into the first sheet. Each should be copied next to each other in a new column. However it copies the data below each other in 1 column.

Thanks in advance

Sub CopyIt()

    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.name <> "All Data" Then
            ws.Range("I106:I160").Copy
            Sheets("All Data").Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 110

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

Try this:

Sub CopyIt()

    Dim ws As Worksheet, c As Range, wsData As Worksheet, wb As WorkBook
    Set wb = ActiveWorkbook
    Set wsData = wb.Sheets("All Data")
    Application.ScreenUpdating = False
    Set c = wsData.Cells(Rows.Count, "B").End(xlUp).Offset(1) 'start here
    For Each ws In wb.Worksheets
        If ws.name <> wsData.Name Then
            With ws.Range("I106:I160")
                c.Resize(.Rows.Count, .Columns.Count).Value = .Value
            End With
            Set c = c.offset(0, 1)'next column 
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Upvotes: 3

Related Questions