Copy and Paste with Ignoring Copying Comments
Hello,
how can i copy and paste (Texts, Formats, Shapes) from a range to another range with ignoring copying comments
Thank you very much.
With wksPasteTo For Each rngArea In rngPasteToRange.Areas: wksCopyFrom.Range(rngArea.Address).Copy .Range(rngArea.Address) '---Copy and Paste Without Comments Next rngArea End with
For example, when i want to disable copying shapes i use this line
Is there a line like (Application.CopyCommentsWithCells = False) to disable copying comments?Application.CopyObjectsWithCells = False
or Is there any method to prevent copying comments?
Last edited by exceere; 03-21-2020 at 06:30 PM.
You could clear the pasted comments.
You don't need the With statement.
For Each rngArea In rngPasteToRange.Areas wksCopyFrom.Range(rngArea.Address).Copy Destination:=rngArea rngArea.ClearComments Next rngArea
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
AlphaFrog, Thank you very much.
but this will clear the already existing comments in the rngArea.
Actually, when you Paste, it clears your pre-existing comments.Originally Posted by exceere
Do you really copy Shapes too? If no, use this...
For Each rngArea In rngPasteToRange.Areas wksCopyFrom.Range(rngArea.Address).Copy rngArea.PasteSpecial xlPasteValuesAndNumberFormats rngArea.PasteSpecial xlPasteFormats Next rngArea Application.CutCopyMode = False
If you do copy shapes, this method will temporarily store the comments 100 columns to the right, and then retrieve them after the paste
For Each rngArea In rngPasteToRange.Areas 'temp store comments rngArea.Copy rngArea.Offset(, 100).PasteSpecial xlComments 'Copy\paste everything wksCopyFrom.Range(rngArea.Address).Copy Destination:=rngArea 'retrieve comments rngArea.ClearComments rngArea.Offset(, 100).Copy rngArea.PasteSpecial xlComments rngArea.Offset(, 100).ClearComments Next rngArea
AlphaFrog, Thank you very much for sharing your mind and time.
Yes, this method solve the issue but it takes a more time to execute (because it has more than 3 operations copy/paste/copy/paste/clear...).
Is there an alternative with less operations.
I don't know of one. Otherwise, I would have suggested it.
It's not that slow. You can speed it up by adding this before and after the For-Next loop
Application.ScreenUpdating = False 'For\Next Application.ScreenUpdating = True
Thank you AlphaFrog,
I use these magic lines always.
at this moment, i used AlphaFrog's idea to overcome this issue.
But, waiting to another guy to share his mind to overcome this issue with less lines.
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks