Is it possible?

[STEVEB]

Hi,

I was wondering if it is possible to copy an entire sheet based on matching cell values? For example of Have a range of cells in column A sheet 1 spreadsheet 1. If any of the #'s in the range match cell b2 on spreadsheet 2, I would like to copy that entire sheet to sheet 2 spreadheet 1.

Any help would be greatly appreciated.

Thanks

[Bob Phillips]

Sub CopyData()
Dim rng1 As Range, rng2 As Range

Set rng1 = ActiveSheet.UsedRange
Set rng2 = Worksheets("Sheet2").UsedRange
If ActiveSheet.Evaluate("=SUMPRODUCT(COUNTIF(" & rng1.Address & _
"," & rng2.Address(, , , True) & "))") > 0 Then
ActiveSheet.UsedRange.Copy
Worksheets("Sheet2").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
End If

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I was wondering if it is possible to copy an entire sheet based on
> matching cell values? For example of Have a range of cells in column A
> sheet 1 spreadsheet 1. If any of the #'s in the range match cell b2 on
> spreadsheet 2, I would like to copy that entire sheet to sheet 2
> spreadheet 1.
>
> Any help would be greatly appreciated.
>
> Thanks
>
>
> --
> STEVEB
> ------------------------------------------------------------------------
> STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
> View this thread: http://www.excelforum.com/showthread...hreadid=500697
>

[STEVEB]

Hi Bob,

Thanks for your help, I really appreciate it!

I was unable to get the code to work. It was not matching any of the cells in the range to cell b2 on the other spreadsheet & then copying the information. Do you have an idea of where I am going wrong?

Thanks again!

[Bob Phillips]

Can u post the book somewhere, on the web, not to the NG?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
>
> Thanks for your help, I really appreciate it!
>
> I was unable to get the code to work. It was not matching any of the
> cells in the range to cell b2 on the other spreadsheet & then copying
> the information. Do you have an idea of where I am 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=500697
>

[STEVEB]

Do you have an e-mail I could send it to?

[Bob Phillips]

bob dot phillips at tiscali dot co dot uk

do the obvious

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> Do you have an e-mail I could send it to?
>
>
> --
> STEVEB
> ------------------------------------------------------------------------
> STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
> View this thread: http://www.excelforum.com/showthread...hreadid=500697
>