This is what I have currently and it is working fine. The
only thing I want to add is some coluymnwidth and borders
after I paste some values in the 2003.xls = year(NOW)
& ".xls" inside the new worksheet 13-10-2003 = worksheet
(Date(NOW))

Sub Open_MyFile()
Dim MyDate, MyValues
Application.ScreenUpdating = False
Sheets("Prices ARGO").Activate
MyDate = Now
Application.Workbooks.Open ("C:\Prices\" & Year
(MyDate) & ".xls")
Dim ShName As String
Dim nChr As Integer
ShName = CStr(Date)
For nChr = 1 To Len(ShName)
If Mid(ShName, nChr, 1) = "/" Then Mid(ShName,
nChr, 1) = "-"
Next
Set Wks = Worksheets.Add(After:=Worksheets
(Worksheets.Count))
With Wks
.Name = ShName
Workbooks("Workbook2").Worksheets("Prices ARGO").Range
("A1:G15").Copy
.Range("A1:A1").VALUE = Selection.PasteSpecial
(Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False)
Workbooks(Year(Now) & ".xls").Save
Workbooks(Year(Now) & ".xls").Close
Sheets("Intro").Activate
MsgBox "" & Day(Now) & "'s prices have been saved
inside " & Year(Now) & ".xls"
End With
Application.ScreenUpdating = True
End Sub


Can you please help me out I have tried so many different
ways and notrhing seems to be the right approach

Thanks for your help

Workbooks("Workbook2.xls").Worksheets( _
"Prices ARGO").Range("A1:G15").Copy

..Range("A1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

--
Regards,
Tom Ogilvy

Antonis wrote in message
...
This is what I have currently and it is working fine. The
only thing I want to add is some coluymnwidth and borders
after I paste some values in the 2003.xls = year(NOW)
& ".xls" inside the new worksheet 13-10-2003 = worksheet
(Date(NOW))

Sub Open_MyFile()
Dim MyDate, MyValues
Application.ScreenUpdating = False
Sheets("Prices ARGO").Activate
MyDate = Now
Application.Workbooks.Open ("C:\Prices\" & Year
(MyDate) & ".xls")
Dim ShName As String
Dim nChr As Integer
ShName = CStr(Date)
For nChr = 1 To Len(ShName)
If Mid(ShName, nChr, 1) = "/" Then Mid(ShName,
nChr, 1) = "-"
Next
Set Wks = Worksheets.Add(After:=Worksheets
(Worksheets.Count))
With Wks
.Name = ShName
Workbooks("Workbook2").Worksheets("Prices ARGO").Range
("A1:G15").Copy
.Range("A1:A1").VALUE = Selection.PasteSpecial
(Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False)
Workbooks(Year(Now) & ".xls").Save
Workbooks(Year(Now) & ".xls").Close
Sheets("Intro").Activate
MsgBox "" & Day(Now) & "'s prices have been saved
inside " & Year(Now) & ".xls"
End With
Application.ScreenUpdating = True
End Sub


Can you please help me out I have tried so many different
ways and notrhing seems to be the right approach

Thanks for your help