Merging single worksheet from different workbooks using VBA

jdhfch

Board Regular
Joined
Jan 25, 2018
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
Hi JLGWhiz

It does work in that I copies the data across, however it leaves loads of empty rows between each workbooks data?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top