Insert Values only

[stockgoblin42]

Hi,

Currently I'm using:

Please Login or Register  to view this content.

Is there a faster way to do this that only inserts the values? I do require an insert, not just a paste.

[NeedForExcel]

With Faster, do you mean a faster Macro or a Smaller Macro?

Coz as far as those above codes are concerned, its fast enough..

[stockgoblin42]

I mean faster, plain and simple because it's in a loop that does it hundreds of times.

I recently learned the faster way of copy and paste without the Selecting and Activating and I was wondering if Insert was like that too.

Also, I want to insert just the values as the above does the formulas too and I don't want them.

[NeedForExcel]

As far as my knowledge is concerned, there is no faster way. The best you can do is turn Screenupdating to false.

This would increase your macro speed.

Thank You,

Deep

[HaHoBe]

Hi, stockgoblin42,

maybe

Please Login or Register  to view this content.

where no change of the active sheet is done.

@Deep:
Any Select or Activate will slow any action down increasingly. ScreenUpdating may speed things up but the slowliness comes from other parts which by using them properly will increase speed without turning Updating off.

Ciao,
Holger

[NeedForExcel]

Noted!!

Thank You,

Deep

[Leith Ross]

Hello stockgoblin42,

If you don't need to copy over the cell formatting then the quickest way is by direct assignment...

Please Login or Register  to view this content.

[HaHoBe]

Hi, Leith,

and that sniplet will insert the cells into the sheet where the values are copied like

Please Login or Register  to view this content.

from the opening post show? I dare to doubt.

CIao,
Holger

[Leith Ross]

Hello Holger,

I forgot to mention, for this method to work the ranges must have the same number of rows and columns. The example below will overwrite any previous information on the second sheet.

Please Login or Register  to view this content.

If the values from the first sheet are to be appended to the existing data then the code would be this...

Please Login or Register  to view this content.

[stockgoblin42]

Hi,


I'm getting an Expected End of Statement with the Offset highlighted when I replace:

Please Login or Register  to view this content.

with your suggestion of:

Please Login or Register  to view this content.

Any ideas Why?

[HaHoBe]

Hi, stockgoblin42,

I bet you can figure it out by youurself if you count out how many rows there are from 2 to 6 - definitely not 1.

And as mentioned the code would always overwrite the same area.

Please Login or Register  to view this content.

Ciao,
Holger

[stockgoblin42]

Oh, I'm looking for an insert, definately not an overwrite

something to replace:

Please Login or Register  to view this content.

but faster, without the selecting

[HaHoBe]

Hi, stockgoblin42,

maybe have a look at the code provided in my first post in this thread.

Ciao,
Holger

[stockgoblin42]

That code is still copying the formulas. I'm just after the values.

Please Login or Register  to view this content.

I tried adding .Value but no luck.

[HaHoBe]

Hi, stockgoblin42,

maybe try

Please Login or Register  to view this content.

Ciao,
Holger

[stockgoblin42]

I rethought it and we missed the obvious:

Please Login or Register  to view this content.

any chance you could help with:

http://www.excelforum.com/excel-form...23#post3247023

thanks for any help