Microsoft ExcelでVBAを使用して一枚に各シートから列または列をコピーし

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