Merging single worksheet from different workbooks using VBA

jdhfch

New Member
Joined
Jan 25, 2018
Messages
28
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jan 25, 2018
Messages
28
Hi JLGWhiz

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

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