Combining multiple workbooks into one omitting headers after first workbook is imported

bobrandom123

New Member
Joined
Apr 19, 2012
Messages
11
Hey everyone,
I've been working with the following script to take a series of .csv files and combine them into one .xls workbook utilizing Excel 2007. It works great but the problem is that all of the headers in the series are the same. How may I modify this script to include the headers on the first workbook imported but omit the headers from the remaining files?

I appreciate your help

Code:
Sub CombineWorksheets()

Dim fName As String, fPath As string, fPathDone As Strong, OldDir As String
Dim LR As Long, NR As Long
Dim wbkOld As Workbook, wbkNew As Workbook, WS As Worksheet

'Setup    
     Application.ScreenUpdating = False
     Application.EnableEvents = False    
     Application.DisplayAlerts = False    

     Set wbkNew = ThisWorkbook    
     wbkNew.Activate    
     Sheets("Sheet1").Activate    

     If MsgBox("Import new data to this report?". vbYesNo) = vbNo Then Exit Sub    

     NR = Range("A" & Rows.Count).End(xlUp).Row + 1

OldDir = CurDir

'Where csv files to be combined are located   
     fPath = "C:\Documents and Settings\Administrator\Desktop\Example\"

'Where csv files are moved to once combined in this master sheet    
     fPathDone = "C:\Documents and Settings\Administrator\Desktop\Example\Imported\"    

     ChDir fPath    
     fName = Dir("*.csv")

'Import a sheet from found file    
     Do while Len(fName) > 0

'Open file    
     Set wbkOld = Workbooks.Open(fName)

'Find last row and copy data    
     LR = Range("A" & Rows.Count).End(xlUp).Row    
     Range("A1:A" & LR).EntireRow.Copy _        
          wbkNew.Sheets("Sheet1").Range("A" & NR)

'Close file    
     wbkOld.Close True

'Next row    
     NR = Range("A" & Rows.Count).End(xlUp).Row + 1

'Move file to converted folder    
     Name fPath & fName As fPathDone & fName

'Ready next filename    
     fName = DirLoop

'Cleanup    
     ActiveSheet.Columns.AutoFit    
     Application.DisplayAlerts = True    
     Application.EnableEvents = True      
     Application.ScreenUpdating = True

'Restores users original working path    
     ChDir OldDir

End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

bobrandom123

New Member
Joined
Apr 19, 2012
Messages
11
So I tried changing the Range to "A2:A" to get the script to omit the headers but what can I do to get it to copy the header on the first worksheet and then omit the header in the remaining worksheets?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top