Hi,
I have Four work Books. Three are contain the data and one is to link
those datas from those three work books. Examples.

WorkBook #1. In cell A1 has customer name
A2 has total amount.

WorkBook #2. In cell A1 has customer name
A2 has total amount.

WorkBook #3. In cell A1 has customer name
A2 has total amount.

WorkBook # 4 will link those workbook #1,2,3's Customer name and the total
amount.

But, if some of the workbook doesn't have the customer name and amount. I
don't want it to show on the workbook #4 and not even the empty space. How do
I write the code for this.

Any help would be very appreciated.

Pull ALL the data from the first 3 book onto a sheet in the final book (name
the sheet xxx_Data). Remove any blank lines and create a pivot table from
the data. It won't ever display blank lines if you don't want it to.

"sighsky" wrote:

Hi,
I have Four work Books. Three are contain the data and one is to link
those datas from those three work books. Examples.

WorkBook #1. In cell A1 has customer name
A2 has total amount.

WorkBook #2. In cell A1 has customer name
A2 has total amount.

WorkBook #3. In cell A1 has customer name
A2 has total amount.

WorkBook # 4 will link those workbook #1,2,3's Customer name and the total
amount.

But, if some of the workbook doesn't have the customer name and amount. I
don't want it to show on the workbook #4 and not even the empty space. How do
I write the code for this.

Any help would be very appreciated.

What do you want done with the total amount from each sheet?
Do you want the sum of the total amount from sheets 1 - 3 on sheet 4?
Or do you want the total amount from each sheet shown on sheet 4

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=37902

I want the total amount from each sheet show on sheet 4.


"bhofsetz" wrote:


What do you want done with the total amount from each sheet?
Do you want the sum of the total amount from sheets 1 - 3 on sheet 4?
Or do you want the total amount from each sheet shown on sheet 4?


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=379029

This isn't pretty but it works (with my limited testing)

Give it a try.


Code:
--------------------
Sub ConsolCust()
Dim S1Rows As Long, x As Long, y As Long
Dim Cust As String, S2Value As Single, S3Value As Single
y = 1
Worksheets(1).Activate
S1Rows = ActiveSheet.UsedRange.Rows.Count
For x = 1 To S1Rows
Cust = Worksheets(1).Range("A" & x)
Sheets("Sheet2").Select
On Error Resume Next
Cells.Find(What:=Cust, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
If Err.Number = 91 Then GoTo NotFound
S2Value = ActiveCell.Offset(0, 1)
Sheets("Sheet3").Select
On Error Resume Next
Cells.Find(What:=Cust, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
If Err.Number = 91 Then GoTo NotFound
S3Value = ActiveCell.Offset(0, 1)
With Sheets(4)
.Cells(y, 1) = Cust
.Cells(y, 2) = Sheets(1).Cells(x, 2)
.Cells(y, 3) = S2Value
.Cells(y, 4) = S3Value
End With
y = y + 1
NotFound:
Next x
End Sub
--------------------


Perhaps someone else may have a more elegant solution to your problem.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=379029