write vb code for excel: there are many worksheets in one excel, combine all the column…
- 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
- 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 SubTo run this code, press
ALT + F11to 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 pressALT + F8to open the "Macro" dialog. Select the "CombineColumnC" macro and click "Run". This will combine the data from Column C into a new "Combined" worksheet.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.