Sao chép một cột hoặc các cột từ mỗi trang tính vào một trang tính bằng VBA trong Microsoft Excel

Option Explicit

Sub CopyColumns()

Dim Source As Worksheet

Dim Destination As Worksheet

Dim Last As Long

Application.ScreenUpdating = False

'Checking whether "Master" sheet already exists in the workbook

For Each Source In ThisWorkbook.Worksheets

If Source.Name = "Master" Then

MsgBox "Master sheet already exist"

Exit Sub

End If

Next

'Inserting new worksheets in the workbook

Set Destination = Worksheets.Add(after:=Worksheets("Main"))

'Renaming the worksheet

Destination.Name = "Master"

'Looping through the worksheets in the workbook

For Each Source In ThisWorkbook.Worksheets





If Source.Name <> "Master" And Source.Name <> "Main" Then



'Finding the last column from the destination sheet

Last = Destination.Range("A1").SpecialCells(xlCellTypeLastCell).Column



If Last = 1 Then

'Pasting the data in the destination sheet

Source.UsedRange.Copy Destination.Columns(Last)

Else

Source.UsedRange.Copy Destination.Columns(Last + 1)

End If

End If

Next

Columns.AutoFit

Application.ScreenUpdating = True

End Sub