Copy Destination: query! 1

[APElliott]

Hi,

I've a bit of code that i'm tidying up and been told that Copy Destination: might be a way to speed thing up slightly, rather than Copy/paste etc.

However, using the Copy Destination: routine I copy the format aswell as the values!

Can I change the code to just paste the values?

Cheers,

Andrew

 

[SkipVought]

Hi,

FromRange.copy
ToRange.PasteSpecial xlPasteValues

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[APElliott]

Cheers Skip,

That what I had before though!

I was looking at one of FAQ's about speeding macro's up and I think Copy Destination was one of the suggestions, but it looks like I won't be able to use it in this case.

Thanks

Andrew

 

[xlbo]

copy: destination has no pastespecial abilities - it is just extended syntax for the standard copy - which copies everything....but it is far quicker as it doesn't put the data on the clipboard. Even faster would be to not use copy at all - if you just wanna get the values, you could use

dim copyArr() as variant
copyArr() = sheets("Sheet1&quot.range("A1:A100&quot
sheets("Sheet2&quot.range("A1:A100&quot = copyArr()

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[APElliott]

Speed's what I'm after!

Cheers Geoff I'll see if I can fit your code around mine!

Thanks,

Andrew

 

[APElliott]

I thought so - I'm struggling with this one!

How would I fit the following into what you suggested Geoff?

Dim c As Range
Dim t As Range
Dim s As Range

For Each c In Intersect(ActiveSheet.UsedRange, Range("i:i&quot)
If c.Value > 0 Then
Set t = Intersect(c.EntireRow, Union(Range("A:B&quot, Range("E:G&quot, Range("I:I&quot))
If s Is Nothing Then
Set s = t
Else
Set s = Union(s, t)
End If
End If
Next c
If s Is Nothing Then
MsgBox "No Labour Resources Have Been Used! If You Have, Check That They're In The Correct Column!", vbInformation, "Labour Resources"
Sheets("Labour Report&quot.Activate
[a4].Select

Else
s.Copy
Sheets("Labour Report&quot.[a4].PasteSpecial xlPasteValues

Thanks,

Andrew

 

[xlbo]

you have an if s is nothing statement before you set s.....s will therefore always be nothing

Can s be greater than 1 cell ??

not sure I totally understand what you are doing but you are looping cell by cell and picking up an intersection of ranges so the array method will be tricky to work with


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[APElliott]

Hi Geoff,

The code basic copies cells in columns A:B, E:G, & I and for rows where the value in I are greater than 0.

It's something Zathras kindly did for us!

Cheers,

Andrew

 

[xlbo]

aaaah - misread the code at 1st you are building a complex range and then pasting it....
why not use autofilter - filter on i>0 and copy that out instead of looping ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Stripester]

Hi Geoff,

Something like this:

Sub FilterTest()
LRow = Range("a65536&quot.End(xlUp).Row
Columns(&quot;i:i&quot.AutoFilter Field:=1, Criteria1:=&quot;>0&quot;, Operator:=xlOr,Criteria2:=&quot;<0&quot;
Range(&quot;A2:B&quot; & LRow & &quot;, E2:F&quot; & LRow & &quot;, I2:I&quot; & LRow).Copy Destination:=Range(&quot;Sheet3!a2&quot
Columns(&quot;i:i&quot.AutoFilter
End Sub

This still gives me the problem of pasting formats that I don't want, but I can change that to ToRange.PasteSpecial xlPasteValues. Unless there's faster way similar to what you suggested before!

Cheers,

Andrew

 

[xlbo]

Looks pretty good - Think you're stuck with the pastespecial I'm afraid but the autofilter should be faster than a loop - as long as there are plenty of records

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244