Loop Through Workbooks & Worksheets in Folder

delete some superfluous rows

this requires some better definition.

Then, because each workbook's first worksheet...

Is this in addition to the iteration of all worksheets or after the first worksheet?

Delete some superfluous rows....
The worksheets have a number of rows - many of which are blank - above what I consider to be a header column and which need to be deleted.   Not all of the rows are blank however, but what is definitive is that the header row column A will have one of three words in it (X, Y or Z).  When the code comes up to that word in A1 then it should stop deleting rows.  

Yes, renaming the worksheet would be in addition to, but after the above task.

...will have one of three words in it (X, Y or Z)

Are "X, Y or Z" placeholders for actual words or is it one of the three (last) letters of the alphabet?

Placeholders for actual words.

please post a representative sample of one of the workbooks along with actual representative (X,Y,Z) words

Sensitive Data Removed.  
The word I'd look for in this case is Char(10) & "SAM"
I'd want to lop off everything above that but not including the row with SAM
For-EE.xls

There is nothing in Sheet1!D5

Understood.  There would be were it not for removal if sensitive data.  The name in column D is consistent all the way down.   Call it DrugX.  It could be any row of D for that matter could be D2 - immediately after the header row that begins with SAM.

I don't really care what the data is.  I asked for a representative sample of the data.  Don't remove data that is required for any action you desire.

A representative workbook should have:
* more than one worksheet
* representative data in each worksheet
* enough rows of data such that the D5 cell value (in your problem specification) will always be true.

Not necessary, but helpful:
* different values in the first non-header cell in column A of different worksheets

I can recreate one - but I cannot post the confidential information.   If you can't move forward without it then I can only say think you.  I do  appreciate the help you provided to this point.   I am concurrently working on something - this is close but I'm stuck.

'Loop through all files in a folder
Dim fileName As Variant
Dim FName As Variant

fileName = Dir("C:\Users\User\Desktop\EOQ\*Copy*")

While fileName <> ""


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objworkbook = objExcel.Workbooks.Open("C:\Users\User\Desktop\EOQ\" & fileName)

    
Dim WS As Worksheet
Set WS = ActiveWorkbook.ActiveSheet
For Each WS In ActiveWorkbook.Worksheets
WS.Activate

I = 1
 
Do Until objExcel.Cells(I, 1).Value = Chr(10) & "SAM" Or objExcel.Cells(I, 1).Value = "SAM" Or objExcel.Cells(I, 1).Value = "Group #" Or objExcel.Cells(I, 1).Value = "ParentBPID" Or objExcel.Cells(I, 1).Value = "CPC" Or objExcel.Cells(I, 1).Value = "Sales Rep"

If objExcel.Cells(I, 1).Value <> "SAM" Then

Set objRange = objExcel.Cells(I, 1).EntireRow
objRange.Delete


I = I - 1
End If

I = I + 1

Loop

Next WS

Select allOpen in new window

confidential information

You can change anything that is confidential.  Obfuscate it.  As long as what you post can be used to test any solution to your posted problem/question then it will be helpful/useful for the experts.

LMK if this is sufficient.  It is a very good representation of the data.  The key is that I want anything above the word "SAM" to be deleted.  Each sheet is different in the number of cells that lies above "SAM".   Each sheet is generically named and I want to utilize the consistency of column D in each worksheet to rename that particular sheet.
ForEE2.xls

something like this?
ForEE2.xls

Yes, that would be the desired outcome.

Perfection.   I am very appreciative of your patience and work.  Well done!