Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108755 > unrolled thread

Copy/Destination & Copy/PasteSpecial>Formats SLOW for formats

Back to article view | Back to microsoft.public.excel.programming

Contents

  Copy/Destination & Copy/PasteSpecial>Formats SLOW for formats Phrank <pbeal@hotmail.com> - 2016-04-15 23:30 -0400
    Re: Copy/Destination & Copy/PasteSpecial>Formats SLOW for formats GS <gs@v.invalid> - 2016-04-16 01:21 -0400
      Re: Copy/Destination & Copy/PasteSpecial>Formats SLOW for formats Phrank <pbeal@hotmail.com> - 2016-04-16 09:27 -0400
        Re: Copy/Destination & Copy/PasteSpecial>Formats SLOW for formats GS <gs@v.invalid> - 2016-04-16 13:10 -0400
          Re: Copy/Destination & Copy/PasteSpecial>Formats SLOW for formats Phrank <pbeal@hotmail.com> - 2016-04-16 13:32 -0400

#108755 — Copy/Destination & Copy/PasteSpecial>Formats 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?

[toc] | [next] | [standalone]

#108756

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

[toc] | [prev] | [next] | [standalone]

#108764

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 <gs@v.invalid> 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.

[toc] | [prev] | [next] | [standalone]

#108765

> 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 <gs@v.invalid> 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

[toc] | [prev] | [next] | [standalone]

#108766

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 <gs@v.invalid> 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 <gs@v.invalid> 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...

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web