Copy specific columns from multiple sheets into one sheet

Question

I want to copy all columns from "B" until the end of the sheet into a new sheet named "combined". The Header table in sheets "Combined" is the same of every sheets ("A").

Sub Combine()

   ' Sheets(1).Select
   ' Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireColumn.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    Dim ws As Worksheet
    Dim wsDest As Worksheet

    Set wsDest = Sheets("Combined")

    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> wsDest.Name Then
            ws.Range("B1", ws.Range("B1").End(xlToRight).End(xlDown)).Copy
            wsDest.Cells(1, Columns.Count).End(xlToLeft).Offset("B").PasteSpecial xlPasteValues
        End If
    Next ws

End Sub

and what's the problem you're facing?

DisplayName
– DisplayName

2018-12-23 08:08:40 +00:00
Commented Dec 23, 2018 at 8:08 — DisplayName
– DisplayName, Commented Dec 23, 2018 at 8:08

DisplayName · Accepted Answer · 2018-12-23 08:20:20Z

1

.Offset("B") isn't a valid syntax

to shift one column to the right you want .Offset(, 1)

Dim ws As Worksheet
Dim wsDest As Worksheet

Set wsDest = Sheets("Combined")

For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> wsDest.Name Then
        ws.Range("B1", ws.Range("B1").End(xlToRight).End(xlDown)).Copy
        wsDest.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
    End If
Next ws

answered Dec 23, 2018 at 8:20

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Copy specific columns from multiple sheets into one sheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related