Formula or VBA for copy and paste special

[Ingolf]

Hello!

I need a formula or VBA to copy date from a table from internet (DATA-from Web). This table is updated every day, except weekend, and contain data for 45 days. So every day apear data for today and is delete the oldest day. I take with vlookup date from first table but I need to be carefool to copy from time to time and paste special as value that date.

Town1 20.04.2011 0.1142547
Town2 21.04.2011 0.1095245
...
Town2 26.04.2011 0.1112547

from this table I take with vlookup data from column B an C in an other table and I need a formula to do somethin like "copy-paste special-as value" in the third table. Hope you excuse my bad english and understend what I want.

 

[pbornemeier]

This should work for the code in the last paragraph. Be sure to change the range reference to any cell in your third table.

Option Explicit
 
Sub CurrentRegionPasteSpecial()
    With Range("A1").CurrentRegion 'Change range to be any cell in the 3rd table
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        .Cells(1.1).Select
    End With
    Application.CutCopyMode = False
 
End Sub

 

[Ingolf]

Thank you so much