Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Destination & Copy/PasteSpecialFormats SLOW for formats
I have a 'user' workbook to which users can add notes and highlights.
Each day, they update their 'user' workbook (target) against the updated data workbook (source). I use a separate 'Comments' sheet to copy the project numbers and user notes from the 'user' workbook and also bring in the updated data from the source workbook. There's basically a vlookup to find the matching project numbers and copy/paste the user notes comments and the user's highlighting. In this way, the users are able to maintain their notes and highlighting, while at the same time bringing in the newly updated project numbers and information. It does what it's supposed to do, but for some reason, the copy/paste formats have suddenly become VERY slow. In a nutshell, I'm trying to copy/paste values and formats of two separate columns in one sheet to another sheet. I've tried two separate ways, but no matter what, the pasting of formats takes FOREVER! The first scenario theoretically should be the fastest (Copy Destination). And it IS FAST for values, but not formats. The second scenario of copy/pastespecial yields the same results. Any thoughts? I appreciate any feedback and suggestions. Thanks. With wksTarget LastRowTgt = .Cells(.Rows.Count, "A").End(xlUp).Row ' Copies project numbers in column B to the Comments sheet. Takes less than 1 second to execute. .Range("B1:B" & LastRowTgt).Copy Destination:=wksComments.Cells(1, 1) ' Copies the user notes in column I for the respective project numbers to the comments sheet. Takes approximately 5 minutes to execute. .Range("I1:I" & LastRowTgt).Copy Destination:=wksComments.Cells(1, 5) End With I also tried this: With wksTarget LastRowTgt = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("B1:B" & LastRowTgt).Copy Destination:=wksComments.Cells(1, 1) 'FAST! .Range("I1:I" & LastRowTgt).Copy wksComments.Range("E1").PasteSpecial Paste:=xlPasteValues 'FAST!! wksComments.Range("E1").PasteSpecial Paste:=xlPasteFormats 'SLOOOOOW!!! End With And ultimately, all formatting gets merged on the one wksComments sheet. With wksComments LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("G2:G" & LastRow).Formula = "=IFERROR(VLOOKUP(RC6,R2C1:R56000C5, 2, FALSE)&"""","""")" .Range("H2:H" & LastRow).Formula = "=IFERROR(VLOOKUP(RC6,R2C1:R56000C5, 3, FALSE)&"""","""")" .Range("I2:I" & LastRow).Formula = "=IFERROR(VLOOKUP(RC6,R2C1:R56000C5, 4, FALSE)&"""","""")" Columns("I:I").NumberFormat = "m/d/yyyy" .Range("J2:J" & LastRow).Formula = "=IFERROR(VLOOKUP(RC6,R2C1:R56000C5, 5, FALSE)&"""","""")" ' Copies the formats for user notes in column E to column J .Range("E2:E" & LastRow).Copy .Range("J2:J" & LastRow).PasteSpecial (xlPasteFormats) 'This is VERY VERY SLOW! About 5 minutes to complete 2200 rows. End With Note: there is a project number in every cell for column B (up to about 2500). There is not a user note for every project note. But, the cell (actually the row on the Target sheet) may be highlighted. The same highlighting that is in column B is in column E (because the whole row on the Target sheet is highlighted). Why would the user notes column take SO much longer? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Destination & Copy/PasteSpecialFormats SLOW for formats
Range("Source").Copy Range("Target") does exactly that, formatting and
all. PasteSpecial Paste:=xlPasteFormats must evaluate what to paste and filter on the formatting for each cell, apart from the cell contents. Excel also evaluates the length of cell contents since it's limited to 255 characters. Sounds to me like Excel has more things to deal with when processing user notes! This should take longer without considering any other factors that may contribute. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Destination & Copy/PasteSpecialFormats SLOW for formats
Interesting. And your first sentence below about Range("Source").Copy
Range("Target") is what I use now (and I think I got that from you a little while back). I tried the PasteSpecial PasteFormats because I was having this issue with the first option. As for the User Notes, no one ever enters more than 255 characters - these are simple quick reminder notes; the longer they are the harder it is to quickly see and absorb the info. Here's a possible consideration, though. When this pastes formats, it's also pasting the conditional formatting from the main QueryBuster page. I'm not sure that's the issue, though, because it's doing the same for the other columns that it's copy/pasting in, and there's no delay there. It seems it's something just with the User Notes column E. As for conditional formats, I don't really want or need that at this point in the macro, as the conditional formating gets added at the end. Is there a way to copy/paste just the color highlighting? I'm going to try two things: 1) I'm going to delete column E and reinsert a column - maybe there's something in column E that is causing the delay -- Although I've just tried to do that, and it's taking a VERY long time to delete the column (ref my post in the Excel.Misc group). I think all of these issues are tied to gether, possibly at a workbook level, because these are different sheets, both with the same LONG delay to do a simple task. 2) Recreate the workbook. Not what I want to do, but I'm not sure how else to fix this. Any thoughts? Thanks! Frank On Sat, 16 Apr 2016 01:21:42 -0400, GS wrote: Range("Source").Copy Range("Target") does exactly that, formatting and all. PasteSpecial Paste:=xlPasteFormats must evaluate what to paste and filter on the formatting for each cell, apart from the cell contents. Excel also evaluates the length of cell contents since it's limited to 255 characters. Sounds to me like Excel has more things to deal with when processing user notes! This should take longer without considering any other factors that may contribute. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Destination & Copy/PasteSpecialFormats SLOW for formats
Interesting. And your first sentence below about
Range("Source").Copy Range("Target") is what I use now (and I think I got that from you a little while back). I tried the PasteSpecial PasteFormats because I was having this issue with the first option. As for the User Notes, no one ever enters more than 255 characters - these are simple quick reminder notes; the longer they are the harder it is to quickly see and absorb the info. Here's a possible consideration, though. When this pastes formats, it's also pasting the conditional formatting from the main QueryBuster page. I'm not sure that's the issue, though, because it's doing the same for the other columns that it's copy/pasting in, and there's no delay there. It seems it's something just with the User Notes column E. As for conditional formats, I don't really want or need that at this point in the macro, as the conditional formating gets added at the end. Is there a way to copy/paste just the color highlighting? I'm going to try two things: 1) I'm going to delete column E and reinsert a column - maybe there's something in column E that is causing the delay -- Although I've just tried to do that, and it's taking a VERY long time to delete the column (ref my post in the Excel.Misc group). I think all of these issues are tied to gether, possibly at a workbook level, because these are different sheets, both with the same LONG delay to do a simple task. 2) Recreate the workbook. Not what I want to do, but I'm not sure how else to fix this. Any thoughts? Thanks! Frank On Sat, 16 Apr 2016 01:21:42 -0400, GS wrote: Range("Source").Copy Range("Target") does exactly that, formatting and all. PasteSpecial Paste:=xlPasteFormats must evaluate what to paste and filter on the formatting for each cell, apart from the cell contents. Excel also evaluates the length of cell contents since it's limited to 255 characters. Sounds to me like Excel has more things to deal with when processing user notes! This should take longer without considering any other factors that may contribute. I suspect your workbook has become corrupt! Best to upload as Claus suggests so we can have a look at it... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Destination & Copy/PasteSpecialFormats SLOW for formats
Ok. I'm on my home computer at the moment, so I'll get that uploaded
in a little while. I appreciate the help, especially since about two dozen people in my office use what I've made. Thanks! Frank On Sat, 16 Apr 2016 13:10:30 -0400, GS wrote: Interesting. And your first sentence below about Range("Source").Copy Range("Target") is what I use now (and I think I got that from you a little while back). I tried the PasteSpecial PasteFormats because I was having this issue with the first option. As for the User Notes, no one ever enters more than 255 characters - these are simple quick reminder notes; the longer they are the harder it is to quickly see and absorb the info. Here's a possible consideration, though. When this pastes formats, it's also pasting the conditional formatting from the main QueryBuster page. I'm not sure that's the issue, though, because it's doing the same for the other columns that it's copy/pasting in, and there's no delay there. It seems it's something just with the User Notes column E. As for conditional formats, I don't really want or need that at this point in the macro, as the conditional formating gets added at the end. Is there a way to copy/paste just the color highlighting? I'm going to try two things: 1) I'm going to delete column E and reinsert a column - maybe there's something in column E that is causing the delay -- Although I've just tried to do that, and it's taking a VERY long time to delete the column (ref my post in the Excel.Misc group). I think all of these issues are tied to gether, possibly at a workbook level, because these are different sheets, both with the same LONG delay to do a simple task. 2) Recreate the workbook. Not what I want to do, but I'm not sure how else to fix this. Any thoughts? Thanks! Frank On Sat, 16 Apr 2016 01:21:42 -0400, GS wrote: Range("Source").Copy Range("Target") does exactly that, formatting and all. PasteSpecial Paste:=xlPasteFormats must evaluate what to paste and filter on the formatting for each cell, apart from the cell contents. Excel also evaluates the length of cell contents since it's limited to 255 characters. Sounds to me like Excel has more things to deal with when processing user notes! This should take longer without considering any other factors that may contribute. I suspect your workbook has become corrupt! Best to upload as Claus suggests so we can have a look at it... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy borders without erasing other destination formats? | Excel Discussion (Misc queries) | |||
PasteSpecial: values and formats possible in vbs? | Excel Programming | |||
How to use Object.Copy(Destination) method PasteSpecial(xlPasteVal | Excel Programming | |||
.Copy Destination:= .PasteSpecial ??? | Excel Programming | |||
Copy Number Formats | Excel Programming |