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