Solved: Refresh Pricing Data --> Date

[willemeulen]

I recorded this little macro and attached it to a button.

Now when clicking the button all works fine but sometimes I see the function =TODAY() popping up in a cell a few colums to the right where its intended to happen. As far as I can see the data does get replaced in the cell where I want and this popping up is additional. Can anybody spot an error in the code which causes this strange behavior. To be clear the cell in which the date is added by inserting today and than pasting it with value only is a merged cell(most probably not the best but it works for a novice in VBA).

See code below.

Sub IcledonPrice()
'
' IcledonPrice Macro
' Macro recorded 05/03/2010 by Willemv
'
'
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("F6:G6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.RefreshAll
End Sub

[GTO]

Greetings Willemeulen,

I do not see anything in the code to "go wrong", but would suspect its more at that it "looks like" something is happening that's not, due to the screen repainting.

That said, it also looks like a "straight from the recorder" macro. Not that this isn't a good thing to start with, but since the recorder literally just records your actions, there can be wasted steps, or less than ideal methods employed to get a particular thing done.

In this case, vba has a function which I believe is identical to Today(), called Date.

See if this works:

Sub IcledonPrice()
    Range("F5").Value = Date
    ThisWorkbook.RefreshAll
End Sub

Hope that helps,

Mark

[Bob Phillips]

What exactly is the macro doing? It populates one, dynamic, cell, but then works on two, static, cells.

[willemeulen]

The macro must refresh all queries in the workbook and insert current date into a cell, as what I understand from you the hickup is in the active cell line, that shoud be F5.

W

[GTO]

Yikes, my bad. I meant to type: Range("F6").Value = Date

Given this:

Range("F6:G6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I believe you have cells F6 and G6 merged; correct?

[Bob Phillips]

Then Mark's code should do just what you need.

[willemeulen]

Works a treat GTO.

Cheers