Tom...thanks so much for replying. I keep getting a "Subscript out of range"
error.

Cathy

"Tom Ogilvy" wrote:

bk.Activate
Sheets("DOS5").Select
Sheets("DOS5").Copy After:=Workbooks("template name goes
here????").Sheets(4)

Would be

ThisWorkbook.Worksheets("Dos5").Copy _
After:=bk.Worksheets(bk.Worksheets.count)

--
Regards,
Tom Ogilvy



"Cathy W" wrote in message
...
Actually it is a template that is opened "Dog", then it opens "Cat" to get
the information from. Dos5 is copied from "Cat" to "Dog". How does this
work where it is a template that is opened and not an actual filename that
I
can use?

Here is my code that is stored in the template "Dog":
Private Sub Workbook_Open()
'Dim pathStr As String
'pathStr = "K:\Individual Directories\Woodford, Cathy\Daily Report"

Cells(1, 1).ClearContents

Application.DisplayAlerts = True
UpdateLinks = xlUpdateLinksAlways


If ThisWorkbook.Path = "" Then
Call openfile

End If

'Call openfile
UpdateLinks = xlUpdateLinksAlways


End Sub

Private Sub openfile()

Dim sDailyReport As String
Dim vFileName As Variant
Dim fName As Variant
Dim bk As Workbook
Dim ws As Worksheet
Dim sFilename As String
Dim fileToOpen As Variant


ChDrive "K"
ChDir "K:\Individual Directories\Woodford, Cathy\Daily Report"
fileToOpen = Application.GetOpenFileName("Excel Files (*.xls), *.xls")

If fileToOpen < False Then
Set bk = Workbooks.Open(FileName:=fileToOpen)
Else
MsgBox "User Clicked Cancel, Exiting"
ThisWorkbook.Close savechanges:=False
Exit Sub
End If


ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized

Sheets(1).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(2).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(3).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))
Sheets(4).Cells(1, 1).Value = Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\"))))

THIS IS WHERE I WAS TRYING TO COPY DOS5
'Copy sheet 5 to new book

bk.Activate
Sheets("DOS5").Select
Sheets("DOS5").Copy After:=Workbooks("template name goes
here????").Sheets(4)



'after:=ActiveWorkbook.Sheets("dos5")

'Code that updates the sheets

For Each ws In Worksheets
With ws.Cells
.Copy
.PasteSpecial xlPasteValues
End With
Next ws



'Application.CutCopyMode = False


'Get name for file save
sFilename = ("CNLOPB " & Right(fileToOpen, (Len(fileToOpen) -
(InStrRev(fileToOpen, "\")))))




fName = Application.GetSaveAsFilename(InitialFileName:=sFi lename,
filefilter:="Microsoft Excel Files(*.xls),*.xls", Title:="Select a name
for
this file")
If fName = False Then
bk.Close
ActiveWorkbook.Close savechanges:=False
ThisWorkbook.Close savechanges:=False

Else
ActiveWorkbook.SaveAs fName
bk.Close savechanges:=False
ThisWorkbook.Close savechanges:=False

End If


End Sub


Cathy

"Tom Ogilvy" wrote:

It is unclear where DosV is located - in the original workbook or the
called
file?

Where is the code located? In the original workbook?

Are you using code like

In any event assume the original workbook is named Dog and the called
workbook is named cat and you are copying from dog to cat

With Workbooks("Cat.xls")
workbooks("Dog.xls").Worksheets("DosV").copy After:=
..Worksheets(worksheets.count)
End With


--
Regards,
Tom Ogilvy

"Cathy W" wrote in message
...
Hi. I have a workbook that calls the open dialog to open and minimize
a
file. The code then takes values from this called file and populates
the
first workbook. What I now need to do is copy an entire sheet (DosV)
to
the
new workbook - sheet DosV.

Any suggestions.

Cathy