Snip.PNG

Hi!

I've made a macro that inserts data from several .txt-files and each file open in separate sheets, which generally looks like the attached picture. Next step is to:

Depending on the value in the cell next to the cell with value "HM" (which placement may vary in different sheets) make a copy of an existing sheet, rename it and insert the values in the columns next to the cells with value “D”, “QC” and “FS” in specific columns. When that’s done I would like the macro to erase the used sheet.

If we take the attached picture as an example, the cell next to the “HM-cell” has a value of 7. In this case I would like excel to:

1) Make a copy of the existing sheet named “CPT”
2) Rename it to the name of the active sheet + [CPT] (i.e. “Name [CPT]”)
3) Copy the values next to the “D-cells” in the active sheet and paste into the “CPT-sheet” in cell A5 and downwards
4) Copy the values next to the “QC-cells” in the active sheet and paste into the “CPT-sheet” in cell D5 and downwards
5) Copy the values next to the “FS-cells” in the active sheet and paste into the “CPT-sheet” in cell E5 and downwards
6) Erase the first sheet
7) Start over with next sheet

If the cell next to the “HM-cell” had a value of 8, I would like excel to do almost the same thing, but instead of copying and renaming the existing sheet “CPT” copy the existing sheet “HfA” and rename it to “Name [HfA]”.

I don't know if this make any sense would be so thankful if anybody could help!