Copy Paste Powershell Excel Keep Formatting

Question

I am trying to automate the process of copying and pasting data but it is important for me to keep the formatting of the cells the same.

I have tried using PasteSpecial(-4163) but that has not worked. Strangely enough it turned some values Bold and other values it un-bold (?).

Here is the function I created to carry out the copying and pasting. Anybody have any suggestions?

function CopyPasteRange
{
    #args[0] = sheet
    #args[1] = row
    #args[2] = column

    $range = $args[0].Cells.Item($args[1], ($args[2])).EntireColumn;
    $range.Copy();
    $args[0].Cells.Item(1, $args[2]).PasteSpecial(-4163);
}

Answer 1

According to the Range.PasteSpecial method's XlPasteType enumeration you are currently pasting values (e.g. xlPasteValues). You will have to run a second PasteSpecial operation using something like 13 (xlPasteAllUsingSourceTheme) as the XlPasteType parameter.

function CopyPasteRange
{
    #args[0] = sheet
    #args[1] = row
    #args[2] = column

    $range = $args[0].Cells.Item($args[1], ($args[2])).EntireColumn;
    $range.Copy();
    $args[0].Cells.Item(1, $args[2]).PasteSpecial(-4163);
    $args[0].Cells.Item(1, $args[2]).PasteSpecial(13);
}

Answer 2

Found the solution! I deleted the -4163 value in PasteSpecial(-4163) and now that copies the cells with formatting and everything.

I feel super special!