Copy columns from different sheets into master sheet in VBA with 1 fixed column

Question

I am trying to make a comparison of offers received from all partners. I have made one workbook where the offers from all partners are added in different sheets. Here, the 1st column is the same for all suppliers viz. the product details. The offer comparison sheet viz. Master sheet should display the offers of all partners.

eg. Column A values in all the sheets of the workbook (Master sheet as well as other sheets) is the same. Column "B, C...n" in Master sheet are blank, while all other sheets have some values in Column B. I need a macro to copy values from Sheet 1.Column B and paste it (paste special) in Master.Column B. Similarly, values from Sheet 2.Column B is to be pasted in Master.Column C and should be repeated for all other sheets in the workbook (Sheets 3, 4....n) into Master.Column (D,E..n).

I tried the following code:

Sub Summary()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets              
     If ws.Name <> "Master" Then
          ws.Range("C2:C10").Copy                                                  
          Sheets("Master").Cells(Rows.Count, 3).End(xlUp).Offset(1).PasteSpecial xlPasteValues                            
     End If        
Next

Application.ScreenUpdating = True

End Sub

However, the output here gets pasted one below the other in the same column instead of in the next adjacent columns.

It is required in corresponding adjacent columns.

Hope I am clear in explaining the requirement. Pls let me know how to get the Column B of "n" sheets in adjacent columns in the Master sheet.

In case it can be done without using the above codes, it is also fine.

Shai Rado Shai Rado · Accepted Answer · 2017-02-28T16:02:50

Try the code below:

Sub Summary()

Dim ws As Worksheet
Dim EmptyCol As Long

Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Master" Then
        ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row).Copy '<-- copy all values in column B
        With Sheets("Master")
            EmptyCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
            .Range(.Cells(1, EmptyCol), .Cells(1, EmptyCol)).PasteSpecial xlPasteValues
        End With
    End If
Next
Application.ScreenUpdating = True

End Sub

Copy columns from different sheets into master sheet in VBA with 1 fixed column

2 Answers