Hi Team,
Hi,
Please see the document/article on how to combine multiple sheets’ data into a single worksheet for your needs for your reference:
Combine Multiple Worksheets into a Single Worksheet
Thank you.
Hi Team,
Hi there,
Dim sourceBook = New Workbook(myDir & “InvestTS_REPORT.xls”)
Dim destbook = New Workbook()
Dim destSheet = destbook.Worksheets(0)
Dim totalRowCount = 0
For Each sheet As Worksheet In sourceBook.Worksheets
Dim sourceRange = sheet.Cells.MaxDisplayRange
Dim destRange = destSheet.Cells.CreateRange(sourceRange.FirstRow + totalRowCount, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount)
'Create an instance of PasteOptions
Dim options = New PasteOptions()
'Set PasteType to All in order to paste formatting, column widths etc
options.PasteType = PasteType.All
destRange.Copy(sourceRange, options)
totalRowCount = sourceRange.RowCount + totalRowCount
Next sheet
destbook.Save(myDir & “OUTPUT.xls”)
Hi There,
Hi Raj,
Dim sourceBook As New Workbook(myDir & “InvestTS_REPORT.xls”)
Dim destbook As New Workbook()
Dim destSheet As Worksheet = destbook.Worksheets(0)
Dim totalRowCount As Integer = 0
Dim iteration As Integer = 0
For Each sheet As Worksheet In sourceBook.Worksheets
'Declare a Range object
Dim sourceRange As Range = Nothing
'Check if first worksheet
If iteration = 0 Then
'Create a range of cells including the header row
sourceRange = sheet.Cells.CreateRange(0,0,sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1)
Else
'Create a range of cells excluding the header row
sourceRange = sheet.Cells.CreateRange(1, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1)
End If
'Create an equal destination range as of source range
Dim destRange As Range = destSheet.Cells.CreateRange(sourceRange.FirstRow + totalRowCount, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount)
'Create an instance of PasteOptions
Dim options As New PasteOptions()
'Set PasteType to All in order to paste formatting, column widths etc
options.PasteType = PasteType.All
'Copy the range while passing the PasteOption object
destRange.Copy(sourceRange, options)
totalRowCount = sourceRange.RowCount + totalRowCount
iteration += 1
Next sheet
destbook.Save(myDir & “OUTPUT.xls”)
Hi there,
Hi Raj,
Hi Babar,
Hi Raj,
'Create an instance of PasteOptions
Dim options As New PasteOptions()
'Set PasteType to All in order to paste formatting, column widths etc
options.PasteType = PasteType.All
'Copy the range while passing the PasteOption object
destRange.Copy(sourceRange, options)
'Set PasteType to ColumnWidths
options.PasteType = PasteType.ColumnWidths
'Copy the range again
destRange.Copy(sourceRange, options)