0

A VBA macro, working in Mac Excel 2011, now yields strange results in Mac Excel 2016. Specifically in the macro below:

  • Step 1 - opens a "portfolio", copy a range of data and close it
  • Step 2 - activates another already opened Workbook and pastes the data there.

But now, instead of data transferred in each cell, I get pasted an image with inside it all the copied data. I don't know if this comes from the copy part or the paste part.

'Step 1'
        Set WbPort(n) = Workbooks.Open(Portfolio(n))
        Sheets(1).Select
        Range("A2:O101").Copy
        Application.CutCopyMode = False
        WbPort(n).Close SaveChanges:=False

'Step 2'
        WbTarget.Activate
        Sheets(1).Select
        Range("C" & Range("AN" & n + 2)).PasteSpecial xlPasteValues
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Studix
  • 351
  • 3
  • 4
  • 15
  • Thanks Mathieu. I could not find the way to present the VBA correctly. – Studix Nov 24 '18 at 17:17
  • Try avoiding select and doing a copy then paste direct, see this (if it helps give it a vote) : https://stackoverflow.com/q/50776026 – Solar Mike Nov 24 '18 at 19:22

1 Answers1

1

Untested.

Since you're copy-pasting values only, it makes sense to skip the clipboard and just assign the values directly to the destination range.

Set WbPort(n) = Workbooks.Open(Portfolio(n))

Dim rangeToCopy as range
Set rangeToCopy = WbPort(n).Worksheets(1).Range("A2:O101")

With WbTarget.Worksheets(1)
.Range("C" & .Range("AN" & n + 2).value2).resize(rangeToCopy.rows.count, rangeToCopy.columns.count).value2 = rangeToCopy.value2
End with

WbPort(n).Close SaveChanges:=False

If that works for you but you still want to fix your existing code, might be worth trying:

Set WbPort(n) = Workbooks.Open(Portfolio(n))
WbPort(n).Worksheets(1).Range("A2:O101").Copy

With WbTarget.Worksheets(1)
.Range("C" & .Range("AN" & n + 2).value2).PasteSpecial xlPasteValues
End with

Application.CutCopyMode = False
WbPort(n).Close SaveChanges:=False

Or instead of xlPasteValues try another xlPasteType(https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype) e.g. xlPasteAll just to see if you still get an image.

Also, you seem to be pasting to a row number determined by the value in Range("AN" & n + 2) of the first sheet of WbTarget -- where n is some variable (in a loop maybe?). I would assign the value in Range("AN" & n + 2).value2 to a long type variable and check that you're pasting to the correct range/cell address.

chillin
  • 4,391
  • 1
  • 8
  • 8
  • Your VBA "untested" section works and is fine for me. I understand that the problem comes in connection to the clipboard. Thank you chillin. – Studix Nov 26 '18 at 14:27