VBA pasting is pasting image, not cells

[gallen]

I've got this working on a couple of other similar sheets but I must be missing something here.

In short I'm opening a workbook, copying a range, pasting the range in to another Workbook.

However, now when I paste it is pasting an image file not the actual cells.

My code is here:

Private Sub ImportPlans()

 '****************************************************************************************************************
    'Sub to open all plans with same Week Commencing date as the one on the sheet and import them into this workbook*
    '****************************************************************************************************************
    
    On Error GoTo errHandle
    Dim Summary As Worksheet
    Dim ImportFrom As Workbook 'Runplan to import
    Dim sFileLoc As String 'location of File to open
    Dim sUnit As String
    Dim sYear As String
    Dim sFileName As String
    Dim ExL As New Excel.Application


    Enable False
    
    sUnit = "5" ' for TEST PURPOSES ONLY!. Ultimately, the unit will be passed as a string variable from the values on the "Data" sheet
    
    
    sYear = GetYear
    sFileName = GetFileName(sUnit)
    
    sFileLoc = Sheet3.Range("_Plans_5").Value & sYear & sFileName
    If CheckFileExists(sFileLoc) = False Then
        MsgBox "Error finding Unit: " & sUnit & "'s plan. Please check file exists and is in correct location.", vbCritical, "Can't find file for this week"
        Enable True
        Exit Sub
    End If
    
    'open the workbook with plan to import
    Set ImportFrom = ExL.Workbooks.Open(sFileLoc, True, True)
    
    ImportFrom.Sheets(1).Range("Print_Area").Copy
    Sheet2.Range("_Plan1").PasteSpecial xlPasteAll
    ExL.Application.DisplayAlerts = False
    ImportFrom.Saved = True
    ExL.Application.DisplayAlerts = False
    ExL.Workbooks.Close


    Enable True
Exit Sub


errHandle:
    MsgBox Err.Description
    Enable True
End Sub

What am I missing?

 

[Norie]

Does this work?

 ImportFrom.Sheets(1).Range("Print_Area").Copy Sheet2.Range("_Plan1")

 

[gallen]

"Copy Method of Range Class Failed"

 

[gallen]

I changed

[COLOR=#333333]xlPasteAll[/COLOR]

to

xlPasteValues

and it seems to work but doesn't answer why the former is pasting as an image

 

[Norie]

I used the same Copy code as you have in your code.

Have you either set the print area for the worksheet or created a named range called 'Print_Area' manually, or with code?

You would need to do one of those for there to be a named range called 'Print_Area'.

 

[gallen]

Yes, it's a named range. Range("Print_Area") equates to Range("A1:H27")

 

[Norie]

Why are you creating a new instance of Excel to open the file returned by GetFileName?

 

[gallen]

Only so It doesn't show. If I do it another way, when I open the workbook it pops up very briefly. Doing it this way seems to not show the opened workbook. It was code I found on here somewhere trying to solve the issue of opening a workbook invisible.

As you can see in my code I have:

Enabled False

which just disables events and screenupdating. But even with this it still pops up.

 

[Norie]

I don't know why but I think using another instance of Excel to open the workbook you are copying from could be something to do with the original problem.

Copying between workbooks that are in separate instances of Excel is not the same as copying between separate workbooks in the same instance.

 

[gallen]

Thank you for your input. Much appreciated. I'll give it a try and see if that solves it