Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· openaiHugging Face Suzhen/CodeChat

write vb code for excel: there are many worksheets in one excel, combine all the column…

  1. User

    write vb code for excel: there are many worksheets in one excel, combine all the column C of each worksheets into one combined worksheet, switch column for each worksheet source and show their worksheet name at the first row of the column

  2. Assistant

    Here's a VB code that combines all the data in Column C from each worksheet into a new worksheet called "Combined". It will place the data of each worksheet in separate columns and add the worksheet name in the first row of each column.

    Sub CombineColumnC()
    
        Dim ws As Worksheet
        Dim combinedWS As Worksheet
        Dim sourceSheet As Worksheet
        Dim sourceCol As Range
        Dim lastCol As Long
    
        ' Create a new worksheet for the combined data
        Set combinedWS = ThisWorkbook.Worksheets.Add
        combinedWS.Name = "Combined"
    
        ' Loop through each worksheet in the workbook
        For Each ws In ThisWorkbook.Worksheets
    
            ' Ignore the "Combined" worksheet
            If ws.Name <> "Combined" Then
                Set sourceSheet = ws
    
                ' Find the next empty column in the combined worksheet
                lastCol = combinedWS.Cells(1, combinedWS.Columns.Count).End(xlToLeft).Column
                If lastCol > 1 Then lastCol = lastCol + 1
                
                ' Get column C data from the source worksheet
                Set sourceCol = sourceSheet.Range("C1:C" & sourceSheet.Cells(sourceSheet.Rows.Count, 3).End(xlUp).Row)
    
                ' Copy the source column data to the next empty column in Combined worksheet
                sourceCol.Copy Destination:=combinedWS.Cells(2, lastCol)
    
                ' Add the worksheet name to the first row of the combined column.
                combinedWS.Cells(1, lastCol).Value = sourceSheet.Name
            End If
    
        Next ws
    
    End Sub
    

    To run this code, press ALT + F11 to open the Visual Basic for Applications (VBA) editor in Excel. Then, click "Insert" > "Module" and paste the code into the module. Close the VBA editor and press ALT + F8 to open the "Macro" dialog. Select the "CombineColumnC" macro and click "Run". This will combine the data from Column C into a new "Combined" worksheet.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.