View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default find and paste Blocks

Sub Tester1()
Dim cell As Range, id As String
Dim rStart As Range, rEnd As Range
Dim sh As Worksheet, rng As Range
id = "TC38169"

Set rng = Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
If Application.CountIf(rng, "*TC*") = 0 Then
MsgBox "Wrong Sheet Active"
Exit Sub
End If
For Each cell In rng
If Not rStart Is Nothing Then
If Left(cell.Value, 3) = "TC" Then
Set rEnd = cell.Offset(-1, 0)
Exit For
End If
End If
If cell = id Then
Set rStart = cell.Offset(1, 0)
End If
Next
Set rng = Range(rStart, rEnd)
With ThisWorkbook
Worksheets.Add After:=.Sheets(Sheets.Count)
End With
Set sh = ActiveSheet
rng.EntireRow.Copy Destination:=sh.Range("A1")


End Sub

worked for me with your test file.

--
Regards,
Tom Ogilvy

"Taru" wrote in message
...

Sorry guys but neither of those worked. Somethinglikeant your's just
ran through my document and didn't do anything. And I don't know
enough about VBA to tweek it.

Tom, you've helped me out before and I'm really glad you replied. But
Your's also didn't work either. I just slapped it between a sub () and
an end sub. But again, I don't know enough VBA to edit it. I've
attached a small excel file with samples of what I'm working with.

In sheet1 You'll find the TC ID, which is what I'm searching Sheet2
for. When I find it in Sheet2 I copy and paste everything under the TC
ID until the next TC ID below it. And all the information begins with
GO:

Please help me out guys.:(


+-------------------------------------------------------------------+
|Filename: Help Book.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4908 |
+-------------------------------------------------------------------+

--
Taru
------------------------------------------------------------------------
Taru's Profile:

http://www.excelforum.com/member.php...o&userid=35494
View this thread: http://www.excelforum.com/showthread...hreadid=553372