Title: VBA code to combine two Workbooks into one Worksheet
Tags: excel;vba
Question: I want to know if it is possible to bring two workbooks (sheet1) into one worksheet (master workbook). I need to have two data together in one worksheet.
Any help is really appreciated. Below are screenshots for further details if helps.
Two files:
Fundraise-pages(1).csv
Supporters(1).csv
Please see the attached for further details if help?
Thank you for looking into this.
File 1 and File 2 data - both have the same email addresses.
Importantly I would like a macro to pull data to identify or even match for both email addresses. If found then add it onto a worksheet.
For example on the worksheet (master) there should be a data file 1 on the left hand side and the data file 2 on the right hand side including headings.
I hope you can see my images clearly?
Regards
enter image description here
enter image description here


Here is another answer: Here is an overview to start with:
https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba
Vba Import CSV files to Excel
Proposal:
Using the connection via PowerQuery allows you to further process them as tables and also join/merge them into one while you can see and follow each step of the process.
Comment for this answer: Thank you for your comment.  We need to do with VBA for our users.

Here is another answer: Something like this should do what you want. Feel free to change the code to suit your needs.
Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Delete the sheet &quot;RDBMergeSheet&quot; if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets(&quot;RDBMergeSheet&quot;).Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Add a worksheet with the name &quot;RDBMergeSheet&quot;
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = &quot;RDBMergeSheet&quot;
'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <&gt; DestSh.Name Then
'Find the last row with data on the DestSh
Last = LastRow(DestSh)
'Fill in the range that you want to copy
Set CopyRng = sh.Range(&quot;A1:G1&quot;)
'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count &gt; DestSh.Rows.Count Then
MsgBox &quot;There are not enough rows in the Destsh&quot;
GoTo ExitTheSub
End If
'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below this macro
CopyRng.Copy
With DestSh.Cells(Last + 1, &quot;A&quot;)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, &quot;H&quot;).Resize(CopyRng.Rows.Count).Value = sh.Name
End If
Next
ExitTheSub:
Application.Goto DestSh.Cells(1)
'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:=&quot;*&quot;, _
After:=sh.Range(&quot;A1&quot;), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:=&quot;*&quot;, _
After:=sh.Range(&quot;A1&quot;), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
Source code:
https://www.rondebruin.nl/win/s3/win002.htm
Also, check this out.
https://www.rondebruin.nl/win/addins/rdbmerge.htm
Comment for this answer: Just made a small update to my original post. Try that AddIn. It's pretty flexible in what it can do!!
Comment for this answer: The functions were not highlighted with Ctrl+K. I just fixed it. Please try it again.
Comment for this answer: It works fir for me. You can do @ things.
#1) Change this: Set CopyRng = sh.Range("A1:G1")
To this: Set CopyRng = sh.UsedRange
#2) Hit the F8 key over and over to step through the code line by line. You will have to debug it and determine exactly what the code is doing.
Comment for this answer: I gave you everything you need. Not sure what's going one now. I guess you need to learn VBA a little better, and then you can be more productive. Don't give up!!
Comment for this answer: Hi Thank you for this. I am very new with VBA.  I have two excel files and want to copy both of them (data) onto a worksheet side by side please depending on a match function relying on email addresses if found.  Two files are located in C:\Drive.
Comment for this answer: Hi I copy your codes  and paste it in the macro. I get a prompt error message: Sub or Function not defined.  'Find the last row with data on the DestSh
Last = LastRow(DestSh)
Comment for this answer: Thanks. When run the macro and I get nothing, just blank worksheet. It seems that I cannot see that with the codes not pull 2 files (2 workbooks) from my c\: drive to a worksheet (master).
Comment for this answer: I copy the codes you sent and paste it into a new workbook's macro and then save it as test.xlsm. I press F8 to see the reaction of the code.  I still cannot see this macro workbook to pull data in from each downloaded file to a workbook.
Comment for this answer: Thank you for your time on this matter which is much appreciated. I agree with your points. I won’t give up. Thanks again.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:06/23/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<excel><vba>', 'Title': 'VBA code to combine two Workbooks into one Worksheet', 'ViewCount': 77, 'id': 72737156, 'user_AboutMe': None, 'user_AccountId': 25615937.0, 'user_DisplayName': 'VBANewbie68', 'user_Reputation': 1.0, 'user_Views': 2.0, 'user_id': 19388546.0}}