|re: "1) SWAP sheet does exist."|
Then why did you say this in your original post?
i) We need to create a new sheet name SWAP
re: "2)...but X,Y,Z are dynamically given to the sheet for the count of the DYNA_% sheets are not constant."
This appears to be a repeat of what you said earlier: It is NOT necessary that all the three sheets(DYNA_X,DYNA_Y,DYNA_Z) would get generated . It is possible that only one/two/three of the three sheets (DYNA_X,DYNA_Y,DYNA_Z) would get generated
This issue has been dealt with in the code via the error handling routine. If any of those three sheets doesn't exist, the code simply skips the "Copy/Paste" instructions for that sheet.
re: "3) Once the keyword EARTH is found, the data below the keyword EARTH(not only the column A where EARTH has been found but the entire rows with all the columns)"
Note the use of this instruction. It copies the rows as requested.
Sheets(sht_Name).Rows(srcRw & ":" & lastRw).Copy
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.