Is it possible? VBA Macro for selecting rows by value returned from a column

I have a project to pull data from a large group of workbooks. The data is formatted backwards with the data titles listed in column A and the data listed out horizontally in rows. I want to flip it so I recorded a macro selecting the rows I want to keep and then pasting in a new page with a paste special transpose. Worked like a charm until I tried the same macro on the next worksheet and all the relevant data was in different rows so the data returned was not what I wanted. Code is at the bottom of post.

I need code that will select rows that contain values within a column. That way I can run the macro on each new worksheet and it will only grab the data rows I want.

For example; every row within column A that contains the word Director, Aspect Ratio, or Cast should be selected.

Please excuse me if this is easy, I am still very new to macros and have little training.

Sub Henson()
'
' Henson Macro
'
' Keyboard Shortcut: Option+Cmd+j
'
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "clean data"
Sheets("TV Template").Select
Range("3:3,5:5,13:13,15:15,41:41,43:43,45:45,51:51,71:71,74:74,75:75").Select
Selection.Copy
Sheets("clean data").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
End Sub

Welcome bubbaphat ,

This code will only work the first time because you are working with Sheet1 instead of ActiveSheet.

It would be really helpful if you attached an example of the workbooks so that we can see whet you're up to.

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Thank you so much for your help. I have modified the script an got it to work on a test sheet. Thanks again.