Hi Joel thanks for that. i think it is almost there.

when run the open file "box" opens when you select the file to open the
following error message appears.

Run time error '1004'
Application-defined or object-defined error.
When you click debug the following is highlighted in your code

NewSht.PasteSpecial _
Paste:=xlPasteValues

Hope you can assist further.

Regards David
--
Regards
David


"joel" wrote:


Try this code. I used GetOpenfilename to open the workbook. I also
copies both the Values and the formats (not the formulas).


Sub MakeNewTotals()

Set Mytemplate = ThisWorkbook.Sheets("hscount")

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox ("Cannot Open file - Exiting Macro")
Exit Sub
End If

Set bk = Workbooks.Open(Filename:="fileToOpen")

With bk
'Create Data for sheet like this "01december2009"
DateStr = Format(Date, "ddmmmmyyyy")

'make new worksheet
Set NewSht = .Sheets.Add(after:=.Sheets(.Sheets.Count))
NewSht.Name = DateStr

Mytemplate.Cells.Copy
'paste values
NewSht.PasteSpecial _
Paste:=xlPasteValues

'paste formats
NewSht.PasteSpecial _
Paste:=xlPasteFormats

'save results
.save
End With


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165571

Microsoft Office Help

.