Click here to Skip to main content
14,773,274 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am working to merge multiple workbooks first sheet into a master workbook sheet using MS Office 2013 VBA code. Everything is working fine except when copying data from source sheets it only copies a portion of it (for example sheet A has 190 columns and sheet B has 250 columns and sheet C so on... but it is only importing like 16 columns from Sheet A and 10 from Sheet B but not all of them), here is the code I have written for it:


Any help will be much appreciated :)

What I have tried:

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("C:\Users\hnoorzai\Desktop\Newfolder")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
 
    'Change B3:H to the range your working on and also B in B65536 to any column required.
    bookList.Worksheets(1).Range("B3:H" & Range("B65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
 
    'Below only change "B" column name to your required column name
    Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    bookList.Close
Next
End Sub
Posted
Updated 22-Jul-16 8:52am
v2
Comments
Richard Deeming 22-Jul-16 12:20pm
   
I'd be surprised if that code copies more than seven columns, as you've told it to only copy columns B to H.
Hazmat HN 22-Jul-16 12:43pm
   
Yes I did cause all my data is in those 7 columns but rows are more than 7 !!! so can you help me on that @richard deeming ?
Richard Deeming 22-Jul-16 13:02pm
   
So which is it? Your question says it's not copying all of the columns, but your comment says it's not copying all of the rows.
Hazmat HN 23-Jul-16 1:53am
   
@Richard Deeming oops sorry I meant rows, but any ways that Problem is solved now, but can you please help now sometimes it gives error " run-time error '1004' " while importing data from source sheets

See Range.Cells Property (Excel)[^] for how to use numbers only to create a Range object.
   
Comments
Hazmat HN 23-Jul-16 10:44am
   
@Richard MacCutchan Right now my code is working, but the only problem I get is that whenever the number of workbooks increase more than 5 in the folder I am importing sheets from, I get this error: "Run-time Error 1004"
Is there anyway to workaround this problem ?
Richard MacCutchan 23-Jul-16 11:27am
   
When you receive an error message that you do not understand you should always first ask Google about it: see https://www.google.com/search?q=Run-time+Error+1004.
You can easily get the number of used rows and columns with
myrows= bookList.Worksheets(1).UsedRange.RowS.Count
mycols= bookList.Worksheets(1).UsedRange.ColS.Count


Excel can copy directly WorkSheets between Books.
Sheets(1).Copy After:=MyBook.Sheets(MyBook.Sheets.Count)


Note that Excel 2013 handle far more than 65536 rows.
   
Comments
Hazmat HN 23-Jul-16 10:43am
   
@ppolymorphe Right now my code is working, but the only problem I get is that whenever the number of workbooks increase more than 5 in the folder I am importing sheets from, I get this error: "Run-time Error 1004"
Is there anyway to workaround this problem ?
Patrice T 23-Jul-16 11:55am
   
During the process, you probably open a WorkBook different from the others and you range selection fail.
Use the debugger to see where is the problem in code and in WB.
do you have the problem if you treat this WB first ?
WS protection can also by your problem. Difficult to tell more remotely.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900