Match Cells & copy
 

Hi,

Does anyone have any suggestions for the following:

If Cell A2-Sheet 1 - Spreadsheet A matches
Cell B2 -Sheet 1-Spreadsheet B Then I would like to copy
the entire Sheet 1 from Spreadheet B to Sheet 2 on Spreadsheet A.

Any help would be greatly appreciated!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Steve,

Change the names as appropriate, or use the correct object variables:

If Workbooks("Spreadsheet A.xls").Worksheets("Sheet 1").Range("A2").Value = _
Workbooks("Spreadsheet B.xls").Worksheets("Sheet 1").Range("B2").Value Then
Workbooks("Spreadsheet B.xls").Worksheets("Sheet 1").Cells.Copy _
Workbooks("Spreadsheet A.xls").Worksheets("Sheet 2").Cells
End If

HTH,
Bernie
MS Excel MVP


"STEVEB" wrote in message
...

Hi,

Does anyone have any suggestions for the following:

If Cell A2-Sheet 1 - Spreadsheet A matches
Cell B2 -Sheet 1-Spreadsheet B Then I would like to copy
the entire Sheet 1 from Spreadheet B to Sheet 2 on Spreadsheet A.

Any help would be greatly appreciated!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Thanks Bernie!!! Everything worked great!!!

Would it be possible to expand the formula for:

If any cell in column A Sheet 1 Spreadsheet A matches
Cell B2 -in any of the sheets -Spreadsheet B Then I would like to copy

the entire Sheet (the match) from Spreadheet B to Sheet 2 on
Spreadsheet A.

Thanks again


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Steve,

Dim mySht As Worksheet

For Each mySht In Workbooks("Spreadsheet B.xls").Worksheets
If Not (IsError(Application.WorksheetFunction.Match( _
mySht.Range("B2").Value, Workbooks("Spreadsheet A.xls"). _
Worksheets("Sheet 1").Range("A:A"), False))) Then
mySht.Cells.Copy _
Workbooks("Spreadsheet A.xls").Worksheets("Sheet 2").Cells
MsgBox "Just copied " & mySht.Name
Exit Sub
End If
Next mySht

Note that this only copies one sheet. You could continue looping through by taking out the Exit Sub
line, but it would overwrite the existing data. You could make copies of Sheet 2 in that case - let
us know if you want to go that way.

HTH,
Bernie
MS Excel MVP


"STEVEB" wrote in message
...

Thanks Bernie!!! Everything worked great!!!

Would it be possible to expand the formula for:

If any cell in column A Sheet 1 Spreadsheet A matches
Cell B2 -in any of the sheets -Spreadsheet B Then I would like to copy

the entire Sheet (the match) from Spreadheet B to Sheet 2 on
Spreadsheet A.

Thanks again


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Hi Bernie,

Thanks again for your help, I appreciate it!

When I run the code, I get the following error:

Run Time error 1004 - Unable to get the match property of the worksheet
function class.

Do you know where i might be going wrong?

Thanks again


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Steve,

Remove the .WorksheetFunction - that created an error in one version of
Excel, IIRC.
(But not XP, which is what I tested the code in).

Bernie


"STEVEB" wrote in
message ...

Hi Bernie,

Thanks again for your help, I appreciate it!

When I run the code, I get the following error:

Run Time error 1004 - Unable to get the match property of the worksheet
function class.

Do you know where i might be going wrong?

Thanks again


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Thanks Bernie,

That did the trick...Everything worked great, I appreciate it!!

Now, if I did have two or three matches instead of one:

Would it be possible to have

The first match copy data to Sheet 2 (The code already does this!)
The 2nd match to Sheet 3, the 3rd match to Sheet 4, etc?

Thanks for your help, I appreciate it!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Steve,

Assuming your sheets are named "Sheet 2", "Sheet 3", "Sheet 4" etc."

Bernie


Dim mySht As Worksheet
Dim i As Integer

i = 2
For Each mySht In Workbooks("Spreadsheet B.xls").Worksheets
If Not (IsError(Application.Match( _
mySht.Range("B2").Value, Workbooks("Spreadsheet A.xls"). _
Worksheets("Sheet 1").Range("A:A"), False))) Then
mySht.Cells.Copy _
Workbooks("Spreadsheet A.xls").Worksheets("Sheet " & i).Cells
MsgBox "Just copied " & mySht.Name & " to Sheet " & i
i = i + 1
End If
Next mySht



"STEVEB" wrote in
message ...

Thanks Bernie,

That did the trick...Everything worked great, I appreciate it!!

Now, if I did have two or three matches instead of one:

Would it be possible to have

The first match copy data to Sheet 2 (The code already does this!)
The 2nd match to Sheet 3, the 3rd match to Sheet 4, etc?

Thanks for your help, I appreciate it!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Thanks Bernie!!!! The code worked great!!! Everything went smoothly, I
appreciate it!

Is it possible to add a third spreadheet to the equation?

Spreadsheet C?


Thanks, I really appreciat your help!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Steve,

Thanks Bernie!!!! The code worked great!!! Everything went smoothly, I
appreciate it!

You're welcome. Glad to hear that my macro worked for you.

Is it possible to add a third spreadheet to the equation?

Yes. But it isn't an equation, it's a macro. And almost anything is possible using VBA.

Spreadsheet C?

What would Spreadsheet C do?

And will there be Spreadsheet D, E, F..... ;-)

HTH,
Bernie
MS Excel MVP

Match Cells & copy
 

Hi Bernie,

There would be spreadsheets:

B, C, D, E, F & H.

Spreadsheet C-H would do the same thing as Spreadsheet B:

Find a match with spreadsheet A and copy the sheet to Spreadsheet A.

So basically the current Macro (written for Spreadsheet B only) would
be
expanded to include spreadheets C-H.

Thanks


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Steve,

Assuming you did want to include "G" in that list of workbooks, and all the workbooks are open (the
macro could open the workbook....):

Dim mySht As Worksheet
Dim myLet() As Variant
Dim i As Integer
Dim j As Integer

myLet = Array("B", "C", "D", "E", "F", "G", "H")
i = 2
For j = LBound(myLet) To UBound(myLet)
For Each mySht In Workbooks("Spreadsheet " & myLet(j) & ".xls").Worksheets
If Not (IsError(Application.Match( _
mySht.Range("B2").Value, Workbooks("Spreadsheet A.xls"). _
Worksheets("Sheet 1").Range("A:A"), False))) Then
mySht.Cells.Copy _
Workbooks("Spreadsheet A.xls").Worksheets("Sheet " & i).Cells
MsgBox "Just copied " & mySht.Name & " from " & _
"Spreadsheet " & myLet(j) & ".xls" & " to Sheet " & i
i = i + 1
End If
Next mySht
Next j

HTH,
Bernie
MS Excel MVP


"STEVEB" wrote in message
...

Hi Bernie,

There would be spreadsheets:

B, C, D, E, F & H.

Spreadsheet C-H would do the same thing as Spreadsheet B:

Find a match with spreadsheet A and copy the sheet to Spreadsheet A.

So basically the current Macro (written for Spreadsheet B only) would
be
expanded to include spreadheets C-H.

Thanks


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=505357

Match Cells & copy
 

Hi Bernie,

The Macro worked great! I appreciate all your help

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=50535

Match Cells & copy
 

The Macro worked great! I appreciate all your help.

Thanks for the feedback - I'm always glad to hear when things work well.

Bernie
MS Excel MVP