Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default Copy one worksheet from one workbook to another....

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Copy one worksheet from one workbook to another....

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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default Copy one worksheet from one workbook to another....

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




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Copy one worksheet from one workbook to another....

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






  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default Copy one worksheet from one workbook to another....

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








  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default Copy one worksheet from one workbook to another....

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






  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Copy one worksheet from one workbook to another....

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

--
Regards,
Tom Ogilvy


"Cathy W" wrote in message
...
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" 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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy worksheet from one workbook to a master workbook mvannatta Excel Worksheet Functions 3 April 15th 09 08:32 PM
Copy Worksheet from one Workbook to another Workbook Autumn Dreams Excel Discussion (Misc queries) 5 September 12th 08 10:55 PM
Copy Data from Workbook into specific Worksheet in other Workbook? kingdt Excel Discussion (Misc queries) 1 March 16th 06 06:55 PM
How do I copy a worksheet form a workbook in my workbook Neil Atkinson Excel Programming 1 October 12th 05 12:23 PM
copy worksheet from closed workbook to active workbook using vba mango Excel Worksheet Functions 6 December 9th 04 07:55 AM


All times are GMT +1. The time now is 06:20 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"