VBA pasting is pasting image, not cells

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
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. :confused:

My code is here:

Code:
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?
 
Last edited:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Does this work?
Code:
 ImportFrom.Sheets(1).Range("Print_Area").Copy Sheet2.Range("_Plan1")
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
I changed
Code:
[COLOR=#333333]xlPasteAll[/COLOR]
to
Code:
xlPasteValues
and it seems to work but doesn't answer why the former is pasting as an image
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
Yes, it's a named range. Range("Print_Area") equates to Range("A1:H27")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
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:
Code:
Enabled False
which just disables events and screenupdating. But even with this it still pops up.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
Thank you for your input. Much appreciated. I'll give it a try and see if that solves it
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,947
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top