Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

write vb code for excel: combine one column from multiple worksheet into one worksheet

  1. User

    write vb code for excel: combine one column from multiple worksheet into one worksheet

  2. Assistant

    Here is the VB code to combine one column from multiple worksheets into one worksheet in Excel:

    Sub CombineColumns()

    Dim ws As Worksheet Dim lastRow As Long Dim i As Integer Dim j As Integer Dim k As Integer

    'Count the total number of rows in all worksheets For Each ws In Worksheets lastRow = Application.WorksheetFunction.Max(lastRow, ws.Cells(Rows.Count, "A").End(xlUp).Row) Next ws

    'Create a new worksheet to combine the data Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "CombinedData" Range("A1").Value = "Combined Data"

    'Copy the data from each worksheet k = 1 For Each ws In Worksheets For i = 1 To lastRow 'Copy the data from the first column of the current worksheet Worksheets(ws.Name).Range("A" & i).Copy Destination:=Worksheets("CombinedData").Range("A" & k) k = k + 1 Next i Next ws

    'Auto-fit the columns in the new worksheet Worksheets("CombinedData").Columns.AutoFit

    End Sub

    This code loops through each worksheet and copies the data from the first column into a new worksheet called "CombinedData". The new worksheet is created at the end, after all other worksheets. The data is copied row by row and pasted into the new worksheet, starting at cell A1. Finally, the columns in the new worksheet are auto-fit so that the data is properly displayed.