Merging single worksheet from different workbooks using VBA

[jdhfch]

Hi,

I am trying to merge a single worksheet from 11 workbooks in a single location into a master workbook. I have used the VBA below but I don't want there to be any spaces between the merges and there is on this script

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("MYFILELOCATION")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("C2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

Please can someone help?

Rgds

jdhfch

 

[JLGWhiz]

The code works fine for me. Check in column A on the rows that are bland for formulas that return "" when the criteria is not met. Your copy range parameters might need to be modified to eleminate the problem.

 

[jdhfch]

Hi JLGWhiz

It does work in that I copies the data across, however it leaves loads of empty rows between each workbooks data?

 

[JLGWhiz]

Hi JLGWhiz

It does work in that I copies the data across, however it leaves loads of empty rows between each workbooks data?

It did not leave empty rows for me. That is why I suggested that you check what appears to be a blank row to see if it contains formulas. You can select one of the empty cells in column A between groups of data and look in the formula bar at the top of the screen to see if anything appears there. Or you can use the left and right arrow keys to see if there is data in the row off-screen by selecting a cell in the empty row and pressing 'End' and 'right arrow' or 'left arrow' to see if the cursor goes all the way to both ends of the row. The point is that there must be hidden data in those rows that appear to be blank and these are two ways to find that data if it exists. If you do not understand what I am suggesting, the get a co-worker or friend who has more experience with Excel and ask them to help you interpret the suggestion.