Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I copy borders without erasing other destination formats? RuudS Excel Discussion (Misc queries) 0 April 11th 09 08:59 AM
PasteSpecial: values and formats possible in vbs? D. Pirate Roberts Excel Programming 9 October 6th 05 01:08 PM
How to use Object.Copy(Destination) method PasteSpecial(xlPasteVal RAP Excel Programming 1 August 16th 05 03:58 PM
.Copy Destination:= .PasteSpecial ??? myBasic[_2_] Excel Programming 2 November 12th 04 11:11 AM
Copy Number Formats SteveS[_4_] Excel Programming 9 October 13th 04 02:42 AM


All times are GMT +1. The time now is 01:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"