Retrieve a column of the same name from another workbook

[VBAnewbie82]

I have two large workbooks that I need to combine as one. First report is the budget for specific year (workbook BUDGET with one sheet “Sheet1”) and second is estimate for same year (workbook ESTIMATE with one sheet “Sheet1”). Layout of these are identical as follows, only the numbers or the money in budget columns change:

Row1 = cost center numbers for each column
Row2 = cost center names
Row3 = header “Budget” for budget or estimate or header “Results” for current results
Row 4 to x = actual budgets and results in currency

Example for workbook “BUDGET” Sheet1 could look like this:
5555; 5555; 6666; 6666; 7777; 7777;
MAN; MAN; ICT; ICT; HRM; HRM;
Budget; Result; Budget; Result; Budget; Result;
900; 600; 700; 200; 500; 400;

Example for workbook “ESTIMATE” Sheet1 could look like this:
5555; 5555; 6666; 6666; 7777; 7777;
MAN; MAN; ICT; ICT; HRM; HRM;
Budget; Result; Budget; Result; Budget; Result;
1200; 600; 400; 200; 800; 400;

Example for what I am trying to achieve should look like this:
5555; 5555; 5555, 6666, 6666; 6666; 7777, 7777; 7777;
MAN; MAN; MAN; ICT; ICT; ICT; HRM; HRM; HRM;
Budget; Estimate; Result; Budget; Estimate; Result; Budget; Estimate; Result;
900; 1200; 600; 700; 400; 200; 500; 800; 400;

Macro could make this for a new workbook or use ESTIMATE workbook as the one where it is bringing columns from BUDGET. There are around 90 different cost centers = aroung 180 columns in each workbook and around 600 rows in both of them.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of both sheets. Include 8 or 10 rows of data on each sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[VBAnewbie82]

Here is the budget:

BUDGET.xlsx
	A	B	C	D	E	F	G
1	CCid	5555	5555	6666	6666	7777	7777
2	CCname	MAN	MAN	ICT	ICT	HRM	HRM
3		Budget	Result	Budget	Result	Budget	Result
4	Acc1	900	600	700	200	500	400
5	Acc2	900	600	700	200	500	400
6	Acc3	900	600	700	200	500	400
7	Acc4	900	600	700	200	500	400
8	Acc5	900	600	700	200	500	400
9	Acc6	900	600	700	200	500	400
10	Acc7	900	600	700	200	500	400
Sheet1

Here is the estimate:

ESTIMATE.xlsx
	A	B	C	D	E	F	G
1	CCid	5555	5555	6666	6666	7777	7777
2	CCname	MAN	MAN	ICT	ICT	HRM	HRM
3		Budget	Result	Budget	Result	Budget	Result
4	Acc1	1200	600	400	200	800	400
5	Acc2	1200	600	400	200	800	400
6	Acc3	1200	600	400	200	800	400
7	Acc4	1200	600	400	200	800	400
8	Acc5	1200	600	400	200	800	400
9	Acc6	1200	600	400	200	800	400
10	Acc7	1200	600	400	200	800	400
Sheet1

Here is the final result that should be done with vba:

FINAL.xlsx
	A	B	C	D	E	F	G	H	I	J
1	CCid	5555	5555	5555	6666	6666	6666	7777	7777	7777
2	CCname	MAN	MAN	MAN	ICT	ICT	ICT	HRM	HRM	HRM
3		Budget	Estimate	Result	Budget	Estimate	Result	Budget	Estimate	Result
4	Acc1	900	1200	600	700	400	200	500	800	400
5	Acc2	900	1200	600	700	400	200	500	800	400
6	Acc3	900	1200	600	700	400	200	500	800	400
7	Acc4	900	1200	600	700	400	200	500	800	400
8	Acc5	900	1200	600	700	400	200	500	800	400
9	Acc6	900	1200	600	700	400	200	500	800	400
10	Acc7	900	1200	600	700	400	200	500	800	400
Sheet1

 

[mumps]

Start with both workbooks open. Copy/paste this macro into a regular module in the Budget workbook. Create a sheet named "Estimate" in the Estimate workbook. Change the workbook name (in red) to suit your needs. Run the macro.

Sub GetData()
    Application.ScreenUpdating = False
    Dim lRow As Long, desWB As Workbook, srcWS1 As Worksheet, srcWS2 As Worksheet, desWS As Worksheet, x As Long, lCol As Long, y As Long: y = 2
    Set desWB = Workbooks("Estimate.xlsx")
    Set srcWS1 = ThisWorkbook.Sheets("Sheet1")
    Set srcWS2 = desWB.Sheets("Sheet1")
    Set desWS = desWB.Sheets("Estimate")
    lRow = srcWS1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = srcWS1.Cells(1, Columns.Count).End(xlToLeft).Column
    desWS.Cells(1, 1).Resize(lRow).Value = srcWS1.Cells(1, 1).Resize(lRow).Value
    For x = 2 To lCol Step 2
        With desWS
            .Cells(1, y).Resize(lRow).Value = srcWS1.Cells(1, x).Resize(lRow).Value
            .Cells(1, y + 1).Resize(lRow).Value = srcWS2.Cells(1, x).Resize(lRow).Value
            .Cells(3, y + 1) = "Estimate"
            .Cells(1, y + 2).Resize(lRow).Value = srcWS1.Cells(1, x + 1).Resize(lRow).Value
        End With
        y = y + 3
    Next x
    Application.ScreenUpdating = True
End Sub

 

[VBAnewbie82]

Thank you @mumps it works!
Is there a way of saving this macro in personal macro workbook instead of using copy/paste into a regular module in Budget? I tried it saving, but then it gives "Run-time error '91': Object variable or With block variable not set" and the row it highlights is:

lRow = srcWS1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[mumps]

I don't use a personal macro workbook. However, I believe that the problem is caused by the variable 'srcWS1' referring to Sheet1 of the macro workbook instead of the Budget workbook. You could try to change the reference in this line of code:

Set srcWS1 = ThisWorkbook.Sheets("Sheet1")

to

Set srcWS1 =Workbooks("Budget.xlsx").Sheets("Sheet1")

Change the workbook name to suit your needs.

 

[VBAnewbie82]

Set srcWS1 = ThisWorkbook.Sheets("Sheet1")

to

Set srcWS1 = ActiveWorkbook.Sheets("Sheet1")

That seems to work best. Then I don't have to mind the workbook name where I'm running the macro.

 

[mumps]

Glad it worked out,

 

[VBAnewbie82]

I just noticed that if Budget or Estimate doesn't have identical amount of rows, it doesn't work right. So let's tweek this a bit.

If Budget.xlsx Sheet1 contains only odd account numbers (1, 3, 5 and 7) like this:

Budget.xlsx
	A	B	C	D	E	F	G
1	CCid	5555	5555	6666	6666	7777	7777
2	CCname	MAN	MAN	ICT	ICT	HRM	HRM
3		Budget	Result	Budget	Result	Budget	Result
4	Acc1	900	600	700	200	500	400
5	Acc3	900	600	700	200	500	400
6	Acc5	900	600	700	200	500	400
7	Acc7	900	600	700	200	500	400
Sheet1

and Estimate.xlsx Sheet1 contains:

Estimate.xlsx
	A	B	C	D	E	F	G
1	CCid	5555	5555	6666	6666	7777	7777
2	CCname	MAN	MAN	ICT	ICT	HRM	HRM
3		Budget	Result	Budget	Result	Budget	Result
4	Acc1	1200	600	400	200	800	400
5	Acc2	1200	600	400	200	800	400
6	Acc3	1200	600	400	200	800	400
7	Acc4	1200	600	400	200	800	400
8	Acc5	1200	600	400	200	800	400
9	Acc6	1200	600	400	200	800	400
10	Acc7	1200	600	400	200	800	400
Sheet1

the macro's final results is as follows, so it is missing some accounts:

Estimate.xlsx
	A	B	C	D	E	F	G	H	I	J
1	CCid	5555	5555	5555	6666	6666	6666	7777	7777	7777
2	CCname	MAN	MAN	MAN	ICT	ICT	ICT	HRM	HRM	HRM
3		Budget	Estimate	Result	Budget	Estimate	Result	Budget	Estimate	Result
4	Acc1	900	1200	600	700	400	200	500	800	400
5	Acc3	900	1200	600	700	400	200	500	800	400
6	Acc5	900	1200	600	700	400	200	500	800	400
7	Acc7	900	1200	600	700	400	200	500	800	400
Estimate

...so let't try it this way. Next "screenshots" layouts are as close to real as possible. For each account there is account number in column A and its name in column B. In real life there might be a situation where there is results without budget or vice versa. I put data (money) in 3 digit format to help with error checking. First number is same as cost centers number, seconds matches account number and third is 1 for budget, 2 for estimate and 3 for result.

Original budget would be like this, missing account 4, and some budgets without result or results without budget:

Budget.xlsx
	A	B	C	D	E	F	G	H	I	J
1	CCid		5555	5555	6666	6666	7777	7777	8888	8888
2	CCname		MAN	MAN	ICT	ICT	HRM	HRM	ETC	ETC
3			Budget	Result	Budget	Result	Budget	Result	Budget	Result
4	Acc1	Account name1	511		611	613	711	713	811	813
5	Acc2	Account name2	521	523	621	623	721	723	821	823
6	Acc3	Account name3	531	533	631	633	731	733	831	833
7	Acc5	Account name5	551	553	651	653	751	753	851	853
8	Acc6	Account name6		563						863
9	Acc7	Account name7	571	573	671	673	771	773	871	873
Sheet1

Estimate might look like this with again some estimates (budget -header) without results. Notice that there is now account 4.

Estimate.xlsx
	A	B	C	D	E	F	G	H	I	J
1	CCid		5555	5555	6666	6666	7777	7777	8888	8888
2	CCname		MAN	MAN	ICT	ICT	HRM	HRM	ETC	ETC
3			Budget	Result	Budget	Result	Budget	Result	Budget	Result
4	Acc1	Account name1	512		612	613	712	713	812	813
5	Acc2	Account name2	522	523	622	623	722	723	822	823
6	Acc3	Account name3	532	533	632	633	732	733	832	833
7	Acc4	Account name4	542		642	643	742
8	Acc5	Account name5	552	553	652	653	752	753	852	853
9	Acc6	Account name6	562		662		762		862	863
10	Acc7	Account name7	572	573	672	673	772	773	872	873
Sheet1

And when these two are combined, the end should look like this:

Estimate.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CCid		5555	5555	5555	6666	6666	6666	7777	7777	7777	8888	8888	8888
2	CCname		MAN	MAN	MAN	ICT	ICT	ICT	HRM	HRM	HRM	ETC	ETC	ETC
3			Budget	Estimate	Result	Budget	Estimate	Result	Budget	Estimate	Result	Budget	Estimate	Result
4	Acc1	Account name1	511	512		611	612	613	711	712	713	811	812	813
5	Acc2	Account name2	521	522	523	621	622	623	721	722	723	821	822	823
6	Acc3	Account name3	531	532	533	631	632	633	731	732	733	831	832	833
7	Acc4	Account name4		542			642			742
8	Acc5	Account name5	551	552	553	651	652	653	751	752	753	851	852	853
9	Acc6	Account name6		562	563		662			762			862	863
10	Acc7	Account name7	571	572	573	671	672	673	771	772	773	871	872	873
Estimate

 

[mumps]

I assume that the following scenarios are also possible:
-Either the Budget sheet or the Estimate sheet could have the larger number of rows
-The order of the CCnames in column A is not necessarily the same in each of the two sheets
-There could be a CCname in Budget that is not included in Estimate and vice versa
Are all of these scenarios possible?