Copy worksheet from one workbook to another workbook

[David Ryan]

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

 

[joel]

Try this code. I used GetOpenfilename to open the workbook. I als
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 Su

 

[David Ryan]

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

 

[joel]

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:=xlPasteFormat

 

[David Ryan]

Hi Joel worked a treat!! Thanks