Paste Special without selecting

[duncansancho]

Hi all,
I am trying to clean up some macros by removing all the "select"'s as explained by Skip. I have succeeded in speeding up a lot of processes but am having trouble with copying the result of formulas as a value. I can use the following code to copy a variable length column, but how can I do it when it needs Paste Special?

Range(Sheets("Input&quot.Range("A1&quot, Sheets("Input&quot.Range("A1&quot.End(xlDown)).Copy Sheets("Output&quot.Range("D7&quot

Duncan

 

[papajohn20]

You can simply find the last row of your data as follows:
lrow = Sheets("Input&quot.Range("A1&quot.End(xlDown)

then you could write something like this

for i = 1 to lrow
Sheets("Output&quot.cells(7+i,4) = Sheets("Input&quot.cells(i,1)
next i

So, with this way, you just copy the values and nothin else! Alternative you can record a macro and instead of paste choose paste special - only values. Then use the recorded macro for your need. I thing that will help.

 

[GlennUK]

You still don't need to select. Use soemthing like this:

Range(Sheets("Input&quot.Range("A1&quot, Sheets("Input&quot.Range("A1&quot.End(xlDown)).Copy

Sheets("Output&quot.Range("D7&quot.PasteSpecial Paste:=xlValues

Just have 2 statements to do this instead.


Glenn.

 

[duncansancho]

Thanks to you both - sorry for the delay in replying, I was called away!
Duncan