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

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

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

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

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

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