Matt_Straya
Member
Hi I have a problem I should know but am getting tied up in knots over it. I have multiple XML files that I pull into one Workbook (multiple sheets), then the next bit of code creates the Master sheet and copies all the sheets data into it - all good up to this point. However, I only want the header row from the first sheet, not all the other sheets so all the other sheets except for the first one should only copy across the 3rd row. Any advice/clarity would be welcome!
Code:
Sub CombineCsvFiles()
'Contact CAPU if there are any issues
Dim xFilesToOpen As Variant
Dim L As Long
Dim xRg As Range
Dim I As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
On Error GoTo ErrHandler
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
xDelimiter = "|"
xFilesToOpen = Application.GetOpenFilename("XML Files (*.xml), *.xml", , "CAPU XML Tool", , True)
If TypeName(xFilesToOpen) = "Boolean" Then
MsgBox "No files were selected", , "CAPU XML Tool"
GoTo ExitHandler
End If
I = 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Copy
Set xWb = Application.ActiveWorkbook
xTempWb.Close False
Do While I < UBound(xFilesToOpen)
I = I + 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Move , xWb.Sheets(xWb.Sheets.Count)
Loop
On Error Resume Next
Worksheets.Add Sheets(1)
ActiveSheet.Name = "MasterSheet"
For L = 2 To Sheets.Count
Set xRg = Sheets(1).UsedRange
If L > 2 Then
Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
End If
Sheets(L).Activate
ActiveSheet.UsedRange.Copy xRg
Next
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "CAPU XML Tool"
Resume ExitHandler
End Sub