Excel - copy from another workbook with conditional formatting intact

Question

I've figured out most of this myself, but the part that is hanging me up is being able to paste the conditional formatting.

I want to be able to have a workbook open, run a VBA script to open another workbook, copy a range from it, then paste that to the original workbook.

The most success I've had is recording the macro and make this happen: With the original, target workbook open...

Open the source workbook
Copy range A1:X105
Close source workbook
Paste into worksheet titled "Temp" in target workbook

The problem is that the source workbook contains conditional formatting, and if the source workbook is closed before you paste into the target workbook, the conditional formatting isn't being pasted.

So either I need to find a way to paste the data with the conditional formatting, or I need to be able to switch back to the target workbook before closing the source workbook. This is a process that is going to need to be ran multiple times with different target workbooks, so the VBA code can't refer to a workbook filename for the target. The source workbook will always have the same path though.

Searching the site, I could only find solutions that specified the path for both workbooks.

This is what I have right now:

Sub CopyData() 
Application.DisplayAlerts = False 
Workbooks.Open filename:="source.xlsx", _ 
UpdateLinks:=3 
Range("A1:X105").Select 
Selection.Copy 
ActiveWindow.Close 
Sheets("Temp").Select 
ActiveSheet.Paste 
Application.DisplayAlerts = True 
End Sub

I suppose what I need to implement into this is to declare the target workbook as a variable. Can someone help with that?

So just sounds like your script has to paste from the source workbook to the target workbook before closing the first. This is not a problem even without specifying the workbook name if you set it as a workbook variable. Please provide the code you have developed so far for further assistance. — nbayly
Sub CopyData() Application.DisplayAlerts = False Workbooks.Open filename:="source.xlsx", _ UpdateLinks:=3 Range("A1:X105").Select Selection.Copy ActiveWindow.Close Sheets("Temp").Select ActiveSheet.Paste Application.DisplayAlerts = True End Sub — Robby
Sorry. I don't know how to do pre-formatted text in the comment. — Robby
@Robby you have to put code in the question. ANything more than small one-liners can't render in the comments. — David Zemens
so the VBA code can't refer to a workbook filename for the target. maybe not, but it can most definitely open a FileDialog prompt and programmatically open the workbooks in question... :) — David Zemens

nbayly nbayly · Accepted Answer · 2016-09-28T21:29:45

You can just dim the workbook and then copy and paste. After you have completed that you can then using the variable, close the workbook. Code would be as follows:

Sub CopyData()
    Application.DisplayAlerts = False
    Dim wbSource As Workbook
    Set wbSource = Workbooks.Open(Filename:="source.xlsx", UpdateLinks:=3)
    wbSource.Sheets(1).Range("A1:X105").Copy
    ThisWorkbook.ActiveSheet.Selection.Paste
    wbSource.Close
    Application.DisplayAlerts = True
End Sub

I don't see how you determine what range you paste on the target workbook but will leave that for another question. The answer by Emily Alden I don't think will work because you can't copy from a source that is closed. Clipboard behaves differently with Excel than with other applications.

Excel - copy from another workbook with conditional formatting intact

2 Answers