Selection of data

[SMILE]

Hi
Thanks for the reply but iit did not work in my case, let me explai
it.

I have the data area B8:F20. If letter 'x' finds in col B, then tha
is the last cell. Suppose the 'x' is in B12 then my data will b
B8:F11. I need to select that area and copy and paste in anothe
sheet.

Hope it is clear for you.
Thanks

 

[icestationzbra]

modified according to your latest comment:

Sub CopyPasteOnX()

Dim rngCell As Range
Dim rngAll As Range

With Sheet1

Set rngAll = .Range("a:a")

For Each rngCell In rngAll

If rngCell.Value = "x" Then

Range("a2:b" & rngCell.Row - 1).Copy .Range("f25")

End If

Next rngCell

End With

End Sub

modify to suit your requirements

 

[jeff]

Hi,

Modifying IceStation's code a bit, perhaps this is close?
Option Explicit

Sub CopyPasteOnX()
Dim rngCell As Range
Dim rngAll As Range
With Sheet1
Set rngAll = .Range("b8:b1000")
For Each rngCell In rngAll
If rngCell.Value = "x" Then
Range("B8:F" & rngCell.Row - 1).Copy
Destination:=Sheet2.Range("G1:G1")
End If
Next rngCell
End With
End Sub

jeff