VBA Excel for Mac : Copy Paste
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
excel vba macos
edited Nov 24 '18 at 16:55
Mathieu Guindon
43.7k767150
asked Nov 24 '18 at 16:20
StudixStudix
4819
add a comment |
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
excel vba macos
edited Nov 24 '18 at 16:55
Mathieu Guindon
43.7k767150
asked Nov 24 '18 at 16:20
StudixStudix
4819
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) : stackoverflow.com/q/50776026
– Solar Mike
Nov 24 '18 at 19:22
add a comment |
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
excel vba macos
edited Nov 24 '18 at 16:55
Mathieu Guindon
43.7k767150
asked Nov 24 '18 at 16:20
StudixStudix
4819
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
excel vba macos
excel vba macos
edited Nov 24 '18 at 16:55
Mathieu Guindon
43.7k767150
asked Nov 24 '18 at 16:20
StudixStudix
4819
edited Nov 24 '18 at 16:55
Mathieu Guindon
43.7k767150
asked Nov 24 '18 at 16:20
StudixStudix
4819
edited Nov 24 '18 at 16:55
Mathieu Guindon
43.7k767150
edited Nov 24 '18 at 16:55
Mathieu Guindon
43.7k767150
edited Nov 24 '18 at 16:55
Mathieu Guindon
43.7k767150
43.7k767150
asked Nov 24 '18 at 16:20
StudixStudix
4819
asked Nov 24 '18 at 16:20
StudixStudix
4819
asked Nov 24 '18 at 16:20
StudixStudix
4819
4819
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) : stackoverflow.com/q/50776026
– Solar Mike
Nov 24 '18 at 19:22
add a comment |
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) : stackoverflow.com/q/50776026
– Solar Mike
Nov 24 '18 at 19:22
Thanks Mathieu. I could not find the way to present the VBA correctly.
– Studix
Nov 24 '18 at 17:17
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) : stackoverflow.com/q/50776026
– Solar Mike
Nov 24 '18 at 19:22
Try avoiding select and doing a copy then paste direct, see this (if it helps give it a vote) : stackoverflow.com/q/50776026
– Solar Mike
Nov 24 '18 at 19:22
add a comment |
1 Answer
1
active
oldest
votes
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://docs.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.
answered Nov 24 '18 at 21:00
chillinchillin
1,505134
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
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53460076%2fvba-excel-for-mac-copy-paste%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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://docs.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.
answered Nov 24 '18 at 21:00
chillinchillin
1,505134
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
add a comment |
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://docs.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.
answered Nov 24 '18 at 21:00
chillinchillin
1,505134
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
add a comment |
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://docs.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.
answered Nov 24 '18 at 21:00
chillinchillin
1,505134
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://docs.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.
answered Nov 24 '18 at 21:00
chillinchillin
1,505134
answered Nov 24 '18 at 21:00
chillinchillin
1,505134
answered Nov 24 '18 at 21:00
chillinchillin
1,505134
answered Nov 24 '18 at 21:00
chillinchillin
1,505134
1,505134
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
add a comment |
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
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
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
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53460076%2fvba-excel-for-mac-copy-paste%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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) : stackoverflow.com/q/50776026
– Solar Mike
Nov 24 '18 at 19:22