Combining Multiple Books and removing some rows

[bearstronaut]

Hi,
I've been looking for a solution to my problem for quite some time and am nearly there. The script I have compiles all workbook sheets in a directory into one workbook. Unfortunately the data I have (over 200 workbooks) has a few extra rows at the start of each sheet I need to get rid of for my other scripts to work. Any suggestions on how to bulk remove the first 2 rows of each copied sheet would be much appreciated.

I have:

Sub mergeFiles()
'Merges all files in a folder to a main file.
'use consolidation function to combine sheets.


'Define variables:
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As fileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker)

'Allow the user to select multiple workbooks
tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

'Loop through all selected workbooks
For i = 1 To tempFileDialog.SelectedItems.Count

'Open each workbook
Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'Copy each worksheet to the end of the main workbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet

'Close the source workbook
sourceWorkbook.Close
Next i

End Sub


Thanks in advance

 

[DanteAmor]

Try this

Sub mergeFiles()
    'Merges all files in a folder to a main file.
    'use consolidation function to combine sheets.
    'Define variables:
    Dim numberOfFilesChosen, i As Integer
    Dim tempFileDialog As FileDialog
    Dim mainWorkbook, sourceWorkbook As Workbook
    Dim tempWorkSheet As Worksheet
    
    Set mainWorkbook = Application.ActiveWorkbook
    Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    'Allow the user to select multiple workbooks
    tempFileDialog.AllowMultiSelect = True
    numberOfFilesChosen = tempFileDialog.Show
    
    'Loop through all selected workbooks
    For i = 1 To tempFileDialog.SelectedItems.Count
        'Open each workbook
        Workbooks.Open tempFileDialog.SelectedItems(i)
        Set sourceWorkbook = ActiveWorkbook
        'Copy each worksheet to the end of the main workbook
        For Each tempWorkSheet In sourceWorkbook.Worksheets
[COLOR=#0000ff]            tempWorkSheet.Rows("1:2").Delete[/COLOR]
            tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
        Next tempWorkSheet
        'Close the source workbook
        sourceWorkbook.Close [COLOR=#0000ff]False[/COLOR]
    Next i
End Sub