Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheet from one workbook to another workbook
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 . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheet from one workbook to another workbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheet from one workbook to another workbook
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet from one workbook to a master workbook | Excel Worksheet Functions | |||
How to Copy entire Worksheet from Workbook S to Workbook D | Excel Programming | |||
Copy Data from Workbook into specific Worksheet in other Workbook? | Excel Discussion (Misc queries) | |||
How do I copy a worksheet form a workbook in my workbook | Excel Programming | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions |