Find Key Word in Col A, Select Content, Paste Consecutively on Newly Added Sheet

SUMMARY: Need to Find Key Word in Col A, Select Content, Paste Consecutively on Newly Added Sheet to build a table of content that should be grouped together in one table.

DETAIL:
Need to be able to search through Col A of an active sheet (we'll say Sheet1 is active), locate exact word "DOC" in this case.
The 'hit' is located on A15. I need it to select that row (15)
and all rows beneath it -- up until it encounters a cell in COL A that begins with the all caps word "TABLE".
Let's say it selected rows 15:21 (whatever content exists in those rows should get copied)

The selected/copied range should get pasted on a newly added/empty sheet starting on row 2 (A2).
So at this point, on the new sheet, A2:A8 rows are holding the copied-over data.

The code should go back to the original active sheet (Sheet1) and continue looking for the next 'hit'
and repeat the process..

Let's say the next hit is on A53. It will start selecting rows 53 down to row 59 until it encounters the word "TABLE" in column A again...

Row 60 (A60 has the all caps word "TABLE") so it stops the select/copy just before that row and paste this 2nd selection over to Sheet2 immediately below the content it pasted previously. (and continues to paste down Sheet2 until all hits of chunks of content have built a new table of 'like' content.
*Example file is attached

Re: Find Key Word in Col A, Select Content, Paste Consecutively on Newly Added Sheet

OMG!! That's it! :wowee: :thumbcoo:
I even tested it further by making the tables varied in size (some a few rows, some many rows) and it was smart enough to pick up only what was needed and EVERYTHING that was needed! LOOOOOVE IT!

I've not been able to figure out anything or find anything close -- And to see you didn't have to use pages and pages of code to achieve it blows my mind. Definitely impressive!

I'm going to have to study this to try to figure out what everything is doing - so I can understand it better for constructing helpful things in the future.. If you have the time, I'd greatly appreciate any/all commenting that you might be able to provide within the code. Especially how you were able to make it locate the proper range of content that could vary/dynamically - (sometimes is a few rows of content, sometimes is 20 rows of content).. no idea how the startcell and endcell is doing it's thing - but it's awesome beyond awesome!

Now, on to try to figure out how to get it to put the proper columns content in the proper columns (the secondary problem that's occurring in Col I and J where the TIV content is mixed.. TIV 17,17 then 20... and 18,18 then 25..) Hoping to find something that can arrange "like content" into the same column - or else continue to paste outward to right systematically.. Sounds pretty complex but I'm hopeful there's a way...

Again THANK YOU!! This FIND, SELECT, PASTE is one of the best things I've seen in a long time!
Happy Dancing :dance: and Cheers for you! :ole:

Re: Find Key Word in Col A, Select Content, Paste Consecutively on Newly Added Sheet

Here's a list of all the "TIV"s that I've extracted from all the coders Word docs:
PUBLIC TIV-11
PUBLIC TIV-12
PUBLIC TIV-13
PUBLIC TIV-14
PUBLIC TIV-15
PUBLIC TIV-16
PUBLIC TIV-17
PUBLIC TIV-18
PUBLIC TIV-19
PUBLIC TIV-20
PUBLIC TIV-21
PUBLIC TIV-22
PUBLIC TIV-23
PUBLIC TIV-24
PUBLIC TIV-25
PUBLIC TIV-26
PUBLIC TIV-27

No idea what they are -- mumbo-jumbo coding language tied to Suffix codes, DocID codes, and Yes/No responses.
I'd ask the coders - but they're gone for the wk-end --

THANK YOU SO MUCH for the commenting - I'll go study it now --
I DID ENCOUNTER A PROBLEM -- I've actually been trying to dissect it all ever since you posted it to try to understand it -- because I encountered an issue when I tried to use it in another (almost identical file) -- and it won't work in the file you provided either -- trying to figure out why -- well crap! I just realized I could post it as an attchmt - which would probably mk it simple for you to instantly see WHY the almost identical format won't work! =-/

I've used your file and renamed it with your original sample content and the new content that won't work..
(don't laugh at my highlighting of your code -- lol -- was just trying to figure out the areas where problems might exist)
Please put me out of my misery -- what's wrong with this format that's breaking the code?

I'm getting: RUN TIME ERROR 91 - Object variable or With block variable not set
this line is the one that's highlighted in yellow when I click DEBUG:
.Range(.Cells(rStartCell.Row, "A"), .Cells(rEndCell.Row - 2, LC)).Copy

(even more puzzling-- part of the content copies over to Sheet 2 as you can see - but not the 2nd chunk)

WITH THIS EXAMPLE,
There's 2 chunks of content with the "DOC" refc
Row 71 (A71:K76)
Row 336 ( A336:P346)

=Same code
=The format is very similar - all the "DOC refcs are still in Col 1 (A)
=Made sure something was present in A1:4 to be safe --
=Sheet names were left in tact
=The word "TABLE" is still occurring (used as a stop/End point)
=-(

Re: Find Key Word in Col A, Select Content, Paste Consecutively on Newly Added Sheet

ohhh -- you're so smart..
it's always the "little things" isn't it?
hmm - where's the icon that represents 'hand slapping across forehead in dismay' - duh - lol

I'll make a comment in the top so I'll remember to always put the End/Stop word at the base to close off the content whenever I use it. Amazingly kool and helpful! Thank you again...

In reviewing the commented out areas and code - most seems to make sense - but can you explain the (2) 2's?:
The "-2" in the following area:
'Copy that Range of Cells
.Range(.Cells(rStartCell.Row, "A"), .Cells(rEndCell.Row - 2, LC)).Copy

as well as the +2 in this area:
'Find the New Last Row in the Target Worksheet
wsTgtLR = wsTGT.Range("A" & Rows.Count).End(xlUp).Row + 2

Why the need for the negative and positive 2s - and the significance of "2"?
Thanks again, C.