Copy one worksheet from one workbook to another....

[Cathy W]

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

 

[Tom Ogilvy]

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

 

[Cathy W]

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:=sFilename,
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]

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)

 

[Cathy W]

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

Cathy

 

[Cathy W]

Sorry if this appears twice....Tom, thanks for the replies. I keep getting a
"Subscript out of range" error on the code you just gave me.

Thanks,
Cathy

 

[Tom Ogilvy]

Then you don't have a sheet named Dos5 in the workbook that contains the
code.

As I understood it, you want to copy the sheet Dos5 from the workbook that
containes the code to the workbook that that code opened (bk).