CODEWITHSUNDEEP
vbaexcel
Robby
Robby

Reputation: 827

Excel - copy from another workbook with conditional formatting intact

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...

  1. Open the source workbook
  2. Copy range A1:X105
  3. Close source workbook
  4. 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?

Upvotes: 1

Views: 876

Answers (2)

nbayly
nbayly

Reputation: 2167

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.

Upvotes: 2

Emily Alden
Emily Alden

Reputation: 570

Based on new information:

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

Previous: From:Copy conditional formatting from one cell to another using VBA?

Sub test()
     Sheets("B").[B1].Copy: Sheets("A").[A1:A10].PasteSpecial xlPasteFormats
End Sub

Is a code that will paste the conditional formatting.

Mostly what you need to do is change the order:

  1. Open Source using a Prompt for the User to select file

  2. Copy Range from Source

  3. Paste Range to Original
  4. Close Source

Upvotes: 0

Related Questions