Copy Paste Macro

[chet645]

I created a macro that opens a workbook, copies information from that workbook, then pastes the information into a "destination" workbook. The problem is, conditional formatting exists in the workbook where the information is being copied, and when the information is pasted into the destination workbook, an external link is created.

Is there a way to paste values and formats without the conditional formatting also being pasted. I don't want to create an external link every time I run the macro?

VBA Copy / Paste Code:

ActiveWindow.ActivateNext
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats

Any help would be much appreciated.

Kindest Regards,

Chet

 

[MARK858]

I don't think you can pastespecial formats without the conditional formatting because as it says it is formatting but if you want to still keep the formatting without the rules you can try something like the below which removes the rules afterwards (but if that doesn't do what you want then we will probably have to resort to API calls).

    Dim myCell As Range

    Application.DisplayAlerts = False
    Selection.PasteSpecial Paste:=xlValues
    Selection.PasteSpecial Paste:=xlFormats
    For Each myCell In Selection
        With myCell
            .Font.FontStyle = .DisplayFormat.Font.FontStyle
            .Interior.Color = .DisplayFormat.Interior.Color
            .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
            .Interior.Pattern = .DisplayFormat.Interior.Pattern
            '.Borders.LineStyle = .DisplayFormat.Borders.LineStyle
        End With
    Next myCell
    Selection.FormatConditions.Delete
    Application.DisplayAlerts = True

 

[JLGWhiz]

You can either delete this line of code

Selection.PasteSpecial Paste:=xlFormats

or delete your CFs from the source sheet and just copy the regular formatting, if desired.

 

[chet645]

Hi Mark, great idea. I'm a novice VBA user, but when I scroll through macro step by step, it gets caught in a circle and can't get out. The circle happens here:

For Each myCell In Selection
With myCell
.Font.FontStyle = .DisplayFormat.Font.FontStyle
.Interior.Color = .DisplayFormat.Interior.Color
.Font.Strikethrough = .DisplayFormat.Font.Strikethrough
.Interior.Pattern = .DisplayFormat.Interior.Pattern
'.Borders.LineStyle = .DisplayFormat.Borders.LineStyle
End With
Next myCell

 

[MARK858]

What have you selected?

 

[chet645]

The macro selects and entire worksheet in a target workbook, then pastes it into another workbook for consolidation purposes.

 

[chet645]

would it be helpful if I posted the entire macro?

 

[MARK858]

If the entire sheet in the consolidation workbook is selected then it will loop through all 17,179,869,184 cells won't it?

Try the below which at least restricts it to only the cells with data (as long as your data starts in A1).

   [color=darkblue]Dim[/color] myCell [color=darkblue]As[/color] Range, lr [color=darkblue]As[/color] [color=darkblue]Long[/color], lc [color=darkblue]As[/color] [color=darkblue]Long[/color]

    Application.DisplayAlerts = [color=darkblue]False[/color]
    Selection.PasteSpecial Paste:=xlValues
    Selection.PasteSpecial Paste:=xlFormats

    lc = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, [color=darkblue]False[/color]).Column
    lr = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, [color=darkblue]False[/color]).Row

    [color=darkblue]For[/color] [color=darkblue]Each[/color] myCell [color=darkblue]In[/color] Range(Cells(1, 1), Cells(lr, lc))
        [color=darkblue]With[/color] myCell
            .Font.FontStyle = .DisplayFormat.Font.FontStyle
            .Interior.Color = .DisplayFormat.Interior.Color
            .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
            .Interior.Pattern = .DisplayFormat.Interior.Pattern
            [color=green]'.Borders.LineStyle = .DisplayFormat.Borders.LineStyle[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] myCell

    Selection.FormatConditions.Delete
    Application.DisplayAlerts = [color=darkblue]True[/color]

[