Insert Values only

Hi,

Currently I'm using:

Sheets("Analyzer").Select
          Range("A2:M6").Select
          Selection.copy
          Sheets("Results").Select
          Range("A2").Select
          Selection.Insert Shift:=xlDown

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

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

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

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.

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

Hi, stockgoblin42,

maybe

Sheets("Analyzer").Range("A2:M6").Copy
Sheets("Results").Range("A2").Insert Shift:=xlDown

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

Noted!!

Thank You,

Deep

Hello stockgoblin42,

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

Sheets("Analyzer").Range("A2:M6").Value =  Sheets("Results").Range("A2").Value

Hi, Leith,

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

          Range("A2").Select
          Selection.Insert Shift:=xlDown

from the opening post show? I dare to doubt.

CIao,
Holger

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.

Sheets("Analyzer").Range("A2:M6").Value =  Sheets("Results").Range("A2":M6).Value

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

Sheets("Analyzer").Range("A2:M6").Value =  Sheets("Results").Cells(Rows.Count, "A").End(xlUp)Offset(1, 0).Resize(1, 13).Value

Hi,


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

Sheets("Analyzer").Select
          Range("A2:M6").Select
          Selection.copy
          Sheets("Results").Select
          Range("A2").Select
          Selection.Insert Shift:=xlDown

with your suggestion of:

Sheets("Analyzer").Range("A2:M6").Value =  Sheets("Results").Cells(Rows.Count, "A").End(xlUp)Offset(1, 0).Resize(1, 13).Value

Any ideas Why?

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.

Sheets("Results").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(5, 13).Value = _
    Sheets("Analyzer").Range("A2").Resize(5, 13).Value

Ciao,
Holger

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

something to replace:

Sheets("Analyzer").Select
          Range("A2:M6").Select
          Selection.copy
          Sheets("Results").Select
          Range("A2").Select
          Selection.Insert Shift:=xlDown

but faster, without the selecting

Hi, stockgoblin42,

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

Ciao,
Holger