Insert Copied Cells - but Values only

[davey11372]

I am trying to create a macro in Excel using the 'Insert Copied Cells' function. However when I do this, it copies everything from the source cells (formats, values, formulas) etc. All I want it to copy/insert is the Values only from the source cells.

In other words I want a combination of Insert Copied Cells (so that it forces a row to move down) and PasteSpecial (so that I can choose the Values attribute only) to be copied.

Any help would be appreciated.

Thank you.

 

[jeffreybrown]

It would help to know a few more details about what you are trying to do, but here is a sample...

Inserts a row at row 4, copies H2:K2 and pastes as values on the newly created row.

Sub Macro1()
    Rows("4:4").Insert Shift:=xlDown
    Range("H2:K2").Copy
    Range("A4").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

 

[davey11372]

Thanks for the prompt reply. I will try your script. I am not sure if it matters, but the sequence of your script may be reversed in that:

I have stock prices being dynamically fed into row 15 (each cell in the row has an array formula that retrieves data via a DDE link)

After every minute, I want the value (only value - without the array formula) to be copied and moved down one row. This way, as minutes pass by, the respective columns get populated and I can use the tabulated data to calculate averages ... I hope I am clear enough.

When I stated your script sequence may be reversed, I meant, I need the contents of Row 15 to be first selected/copied, then insert/paste special the values one row below.

If a change is calling, I would appreciate any updates.

Thanks again.

 

[jeffreybrown]

Not sure if this will work for you, but based off of the parameters it seems to work.

Every mininte this macro will run inserting a row at 16 and then taking row 15 and pasting the value in 16. After 3 iterations it will stop so just change the If line to however many iterations you want run.

This line Sheets("Stocks").Cells(1, 1).Value will place a count of how many times the macro has run so far which is A1. If you don't need this take it out or change the sheet name and reference to something that will work for you.

Option Explicit
Dim MacroRun As Long
Sub UpdateStocks()
    Rows("16:16").Insert Shift:=xlDown
    Rows("15:15").Copy
    Rows("16:16").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
Sheets("Stocks").Cells(1, 1).Value = "Macro1 has run " & MacroRun + 1 & " times."
MacroRun = MacroRun + 1
If MacroRun = 3 Then Exit Sub
Application.OnTime Now + TimeValue("00:00:10"), "UpdateStocks"
End Sub

HTH

 

[davey11372]

Thanks much, got it to work after modifying your script a bit.