An alternate for PasteSpecial Paste:=xlPasteFormats

[Dhanya Saseendran]

Hi,

The goal of my macro is -
For a particular condition of a column, (Field 'Detailed Status' when having a value 'Escalated') copy certain columns to a new Excel worksheet.

Points where I need help is
1. I have been using PasteSpecial Paste:=xlPasteFormats while copying as shown below. But this is taking too long a time. It is enough for me if I copy the format from a single field in the masterSheet. But since I did not know how to do it I have been using the below code.
masterSheet.Range(colIndex & index).Copy
escSheet.Range(escColIndex & escSheetIndex).PasteSpecial Paste:=xlPasteFormats
escSheet.Range(escColIndex & escSheetIndex).PasteSpecial Paste:=xlPasteValues
2. Increase the column width after copying to the escSheet
3. escSheet ("Escalation Report") is currently a Worksheet in the same active workbook. And once all the copy is done, I copy this sheet to another workbook and delete the current sheet. I want to copy this new Workbook to a sheet in the Active folder rather than the current "C:\"

ThisWorkbook.Sheets("Escalation Report").Copy
ActiveWorkbook.SaveAs "C:\Escalation Report.xlsx", FileFormat:=51
ThisWorkbook.Sheets("Escalation Report").Delete


Please help me on the above points. Let me know if you need more information