Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Copy worksheet from one workbook to a master workbook | Excel Worksheet Functions | |||
Copy Worksheet from one Workbook to another Workbook | Excel Discussion (Misc queries) | |||
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 |