Hi Guys hope someone can take the time to help

I have a workbook with a sheet called "hscount" I would like to copy the
values of this sheet to another workbook called "hscounttotals" with the
sheet name based on the current day, month & year, eg "01december2009" the
next month "15january2010" and so on.

I want to attached this to a button the user clicks to do the copy.

Note I only want to copy the values each time.
Regards
David

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
>
> .
>

Hi Joel worked a treat!! Thanks
--
Regards
David


"joel" wrote:

>
> I left out cells
>
> from
>
> 'paste values
> NewSht.PasteSpecial _
> Paste:=xlPasteValues
>
> 'paste formats
> NewSht.PasteSpecial _
> Paste:=xlPasteFormats
>
>
> to
>
> 'paste values
> NewSht.cells.PasteSpecial _
> Paste:=xlPasteValues
>
> 'paste formats
> NewSht.cells.PasteSpecial _
> Paste:=xlPasteFormats
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165571
>
> Microsoft Office Help
>
> .
>