VBA code to combine two Workbooks into one Worksheet

[vbanewbie68]

I want to know if it is possible to bring two workbooks (sheet1) into one worksheet (master workbook). Two data 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

Two workbooks have different data. See the headers from each one.

Fundraise-pages(1).csv

Company Name

Person

Email

Title

Created Date

Url

Fundraiser Page ID

Event Page ID

Event Page Title

Event Page URL

Currency

Raised (inc. Gift Aid)

Target

Status

Donors custom code 1

Donors custom code 2

Donors custom code 3

Donors custom code 4

Cons ID

Appeal_Package ID

Fund ID

Tribute ID

Events custom code 1

Events custom code 2

Events custom code 3

Events custom code 4

Team page custom code 1

Team page custom code 2

Team page custom code 3

Team page custom code 4

Supporters(1).csv

Supporter ID

Title

First Name

Last Name

Email

Address

Town

Postcode

Country

Phone

Join Date

Currency

Lifetime Donations

Lifetime Gift Aid

Last Donation Date

Donors custom code 1

Donors custom code 2

Donors custom code 3

Donors custom code 4

Marketing Opt-in Answer

Marketing Opt-in Date

Email Opt-in

Phone Opt-in

SMS Opt-in

Post Opt-in

 

[Coding4Fun]

Sub Test()

Dim wb As Workbook
Dim wb2 As Workbook
Dim x As Long
Dim y As Long
Dim x2 As Long
Dim y2 As Long

Set wb = Workbooks.Open("C:\Users\UserName\Downloads\File1.csv")

x = Cells(Rows.Count, 1).End(xlUp).Row + 1
y = Cells(1, Columns.Count).End(xlToLeft).Column

Set wb2 = Workbooks.Open("C:\Users\UserName\Downloads\File2.csv")

x2 = Cells(Rows.Count, 1).End(xlUp).Row
y2 = Cells(1, Columns.Count).End(xlToLeft).Column

wb.Sheets(1).Range(Cells(2, 1).Address, Cells(x2, y2).Address).Copy Destination:=wb2.Sheets(1).Range(Cells(x, 1).Address, Cells(x, 1).Address)

End Sub

 

[vbanewbie68]

Hi Coding4Fun

Thank you so much for your time on this.

Please see below the link at the bottom as I use XL2BB. This is my first time I use XL2BB. Do let me know if this is not working?

With your codes, it's looking great when I run a test, but just a small thing is that I would like to move the data from the bottom to move up onto the right hand side instead as see the examples of screenshots including headers. I hope the screenshots are helpful.

Also File 1 data and file 2 data- both have the same email addresses. Apologies for not mentioning it in my initial question.

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. Does this make sense?

I hope everything is clear.

I looking forward to hearing from you asap.

Best regards

V

XL2BB.

example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1	Supporter ID	Title	First Name	Last Name	Email	Address	Town	Postcode	Country	Phone	Join Date	Currency	Lifetime Donations	Lifetime Gift Aid	Last Donation Date	Donors custom code 1	Donors custom code 2	Donors custom code 3	Donors custom code 4	Marketing Opt-in Answer	Marketing Opt-in Date	Email Opt-in	Phone Opt-in	SMS Opt-in	Post Opt-in	Company Name	Person	Email	Title	Created Date	Url	Fundraiser Page ID	Event Page ID	Event Page Title	Event Page URL	Currency	Raised (inc. Gift Aid)	Target	Status	Donors custom code 1	Donors custom code 2	Donors custom code 3	Donors custom code 4	Cons ID	Appeal_Package ID	Fund ID	Tribute ID	Events custom code 1	Events custom code 2	Events custom code 3	Events custom code 4	Team page custom code 1	Team page custom code 2	Team page custom code 3	Team page custom code 4
2	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
3	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
4	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
5	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
6	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
7	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
8	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
9	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
10	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
11	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
12	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 1	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2	Existing Data 2
13
14
Sheet1

 

[Coding4Fun]

Sub Test()

Dim wb As Workbook
Dim wb2 As Workbook
Dim x As Long
Dim y As Long
Dim x2 As Long
Dim y2 As Long

Set wb = Workbooks.Open("C:\Users\UserName\Downloads\File1.csv")

x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column + 1

Set wb2 = Workbooks.Open("C:\Users\UserName\Downloads\File2.csv")

x2 = Cells(Rows.Count, 1).End(xlUp).Row
y2 = Cells(1, Columns.Count).End(xlToLeft).Column

wb.Sheets(1).Range(Cells(1, 1).Address, Cells(x2, y2).Address).Copy Destination:=wb2.Sheets(1).Range(Cells(1, y).Address, Cells(1, y).Address)

End Sub

 

[vbanewbie68]

Sub Test()

Dim wb As Workbook
Dim wb2 As Workbook
Dim x As Long
Dim y As Long
Dim x2 As Long
Dim y2 As Long

Set wb = Workbooks.Open("C:\Users\UserName\Downloads\File1.csv")

x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column + 1

Set wb2 = Workbooks.Open("C:\Users\UserName\Downloads\File2.csv")

x2 = Cells(Rows.Count, 1).End(xlUp).Row
y2 = Cells(1, Columns.Count).End(xlToLeft).Column

wb.Sheets(1).Range(Cells(1, 1).Address, Cells(x2, y2).Address).Copy Destination:=wb2.Sheets(1).Range(Cells(1, y).Address, Cells(1, y).Address)

End Sub

That is much better now and thank you for that. I do really appreciate your time on this matter.

I deleted the blank columns because in the middle section in the worksheet it was blank. I use this code Columns("Z:AD").Delete or do you have a better suggestion for that? Please see the codes below.

I would like For file 1 (email address) to match File 2 (email address). If both matches are found then I want them to move to a worksheet (master). I have mentioned this on my previous comment.

Sub Test()

Dim wb As Workbook
Dim wb2 As Workbook
Dim x As Long
Dim y As Long
Dim x2 As Long
Dim y2 As Long
Dim sh As Worksheet

Set wb = Workbooks.Open("J:\PS\FSD_Rest\SOPS_Data\Database Team\fundraise-pages (1).csv")

x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column + 1

Set wb2 = Workbooks.Open("J:\PS\FSD_Rest\SOPS_Data\Database Team\MacrosSupporters (1).csv")

x2 = Cells(Rows.Count, 1).End(xlUp).Row
y2 = Cells(1, Columns.Count).End(xlToLeft).Column


wb.Sheets(1).Range(Cells(1, 1).Address, Cells(x2, y2).Address).Copy Destination:=wb2.Sheets(1).Range(Cells(1, y).Address, Cells(1, y).Address)

'Delete columns range "Z to AD"
Columns("Z:AD").Delete

' Match function to match or vlookup from file 1 and 2 email address and if found then add it to a worksheet.


End Sub




Thank you

Best regards

V

 

[vbanewbie68]

That is much better now and thank you for that. I do really appreciate your time on this matter.

I deleted the blank columns because in the middle section in the worksheet it was blank. I use this code Columns("Z:AD").Delete or do you have a better suggestion for that? Please see the codes below.

I would like For file 1 (email address) to match File 2 (email address). If both matches are found then I want them to move to a worksheet (master). I have mentioned this on my previous comment.

Sub Test()

Dim wb As Workbook
Dim wb2 As Workbook
Dim x As Long
Dim y As Long
Dim x2 As Long
Dim y2 As Long
Dim sh As Worksheet

Set wb = Workbooks.Open("J:\PS\FSD_Rest\SOPS_Data\Database Team\fundraise-pages (1).csv")

x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column + 1

Set wb2 = Workbooks.Open("J:\PS\FSD_Rest\SOPS_Data\Database Team\MacrosSupporters (1).csv")

x2 = Cells(Rows.Count, 1).End(xlUp).Row
y2 = Cells(1, Columns.Count).End(xlToLeft).Column


wb.Sheets(1).Range(Cells(1, 1).Address, Cells(x2, y2).Address).Copy Destination:=wb2.Sheets(1).Range(Cells(1, y).Address, Cells(1, y).Address)

'Delete columns range "Z to AD"
Columns("Z:AD").Delete

' Match function to match or vlookup from file 1 and 2 email address and if found then add it to a worksheet.


End Sub




Thank you

Best regards

V

Hi could you let me know if that is possible to either to use Vlookup or match function to rely on both emails?
Thanks

Regards