| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet into another workbook
Hello,
I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
You need a set in front of the following line
from: newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") to: set newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") "Brennan" wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
Once you select the new workbook all code that is not otherwise referenced
will refer to the new book. So Sheets("Sheet4") is referenced to your new workbook and not your original workbook. Two choicies 1. Sheets("Sheet4").Select 'Not necessary newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If ThisWorkbook.Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) 2. Sheets("Sheet4").Select 'Not necessary newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Thisworkbook.Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) -- HTH... Jim Thomlinson "Brennan" wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
Have you tested this code? I can not see how it will work. GetOpenFilename
returns a string so set will generate a type mismatch at best. If newFN is declared as string then it will not even compile. -- HTH... Jim Thomlinson "Joel" wrote: You need a set in front of the following line from: newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") to: set newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") "Brennan" wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
Jim you are right. did you test your code. just noticed a small problem.
"NewFM" shouldn't be in double quotes. From Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) to Sheets("Sheet4").Move Befo=Workbooks(NewFN).Sheets(1) You also need to remove the pathname from NewFN. "Jim Thomlinson" wrote: Have you tested this code? I can not see how it will work. GetOpenFilename returns a string so set will generate a type mismatch at best. If newFN is declared as string then it will not even compile. -- HTH... Jim Thomlinson "Joel" wrote: You need a set in front of the following line from: newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") to: set newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") "Brennan" wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
Good point. I just copied the code from the OP. I was assuming that all was
tested prior to posting. Bad assumption on my part. I use objects when I code so mine tends to look quite different. That being said there are a cople of error that I should have picked up on... This is tested... Here would be my take on it... Dim newFN As String Dim wbkOpen As Workbook newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls", _ Title:="Please select a file") If newFN = "False" Then MsgBox "Stopping because you did not select a file" Exit Sub Else Set wbkOpen = Workbooks.Open(Filename:=newFN) End If ThisWorkbook.Sheets("Sheet1").Move Befo=wbkOpen.Sheets(1) -- HTH... Jim Thomlinson "Joel" wrote: Jim you are right. did you test your code. just noticed a small problem. "NewFM" shouldn't be in double quotes. From Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) to Sheets("Sheet4").Move Befo=Workbooks(NewFN).Sheets(1) You also need to remove the pathname from NewFN. "Jim Thomlinson" wrote: Have you tested this code? I can not see how it will work. GetOpenFilename returns a string so set will generate a type mismatch at best. If newFN is declared as string then it will not even compile. -- HTH... Jim Thomlinson "Joel" wrote: You need a set in front of the following line from: newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") to: set newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") "Brennan" wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
I made the change that you suggested and that particular line now errors out.
Any suggestions. Thanks again for the help. "Joel" wrote: You need a set in front of the following line from: newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") to: set newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") "Brennan" wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
Jim and Joel,
Thank you both, you have both been very helpful. Every time I run the code that Jim suggested I get a "Subscript out of range" error message. I appreciate any help that you can offer. Thanks "Jim Thomlinson" wrote: Once you select the new workbook all code that is not otherwise referenced will refer to the new book. So Sheets("Sheet4") is referenced to your new workbook and not your original workbook. Two choicies 1. Sheets("Sheet4").Select 'Not necessary newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If ThisWorkbook.Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) 2. Sheets("Sheet4").Select 'Not necessary newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Thisworkbook.Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) -- HTH... Jim Thomlinson "Brennan" wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
Dim NewWkbk as workbook
dim NewFN as Variant 'could be false dim wksToMove as worksheet set wkstoMove = activeworkbook.worksheets("sheet4") newfn = application.getopenfilename(filefilter:="Excel Files, *.xls", _ title:="Please select a file") if newfn = false then 'user hit cancel exit sub '??? end if set newwkbk = Workbooks.Open(Filename:=newFN) wkstomove.move _ befo=newwkbk.sheets(1) ======== But I'm confused about moving or copying. Your code showed .Move, but your description/subject line says copy. And I'm confused about where "Sheet4" is located. Is it in the activeworkbook when you start the macro? That's what I guessed. ps. You have a couple of problems with this line: Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) First, NewFN wants to be treated as a variable--not a string. (but this won't work, either.) Sheets("Sheet4").Move Befo=Workbooks(NewFN).Sheets(1) Second, NewFN includes the drive, path and filename from the .getopenfilename line. You would use just the filename (not the drive and not the path) in the workbooks collection. This is ok: Workbooks("book1.xls").Activate This won't work: Workbooks("c:\yourpath\yourfolder\book1.xls").acti vate I avoided the trouble by using a workbook variable that would represent that newly opened workbook. pps. Untested, uncompiled. Watch for typos! Brennan wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet into another workbook
Thanks Dave
Your code worked perfectly. Thanks to Jim and Joel as well. B "Dave Peterson" wrote: Dim NewWkbk as workbook dim NewFN as Variant 'could be false dim wksToMove as worksheet set wkstoMove = activeworkbook.worksheets("sheet4") newfn = application.getopenfilename(filefilter:="Excel Files, *.xls", _ title:="Please select a file") if newfn = false then 'user hit cancel exit sub '??? end if set newwkbk = Workbooks.Open(Filename:=newFN) wkstomove.move _ befo=newwkbk.sheets(1) ======== But I'm confused about moving or copying. Your code showed .Move, but your description/subject line says copy. And I'm confused about where "Sheet4" is located. Is it in the activeworkbook when you start the macro? That's what I guessed. ps. You have a couple of problems with this line: Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) First, NewFN wants to be treated as a variable--not a string. (but this won't work, either.) Sheets("Sheet4").Move Befo=Workbooks(NewFN).Sheets(1) Second, NewFN includes the drive, path and filename from the .getopenfilename line. You would use just the filename (not the drive and not the path) in the workbooks collection. This is ok: Workbooks("book1.xls").Activate This won't work: Workbooks("c:\yourpath\yourfolder\book1.xls").acti vate I avoided the trouble by using a workbook variable that would represent that newly opened workbook. pps. Untested, uncompiled. Watch for typos! Brennan wrote: Hello, I am trying to automate the copying of a sheet into another workbook that I have to select and I am not able to get my code to work. This is what I have so far: Sheets("Sheet4").Select newFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") If newFN = False Then MsgBox "Stopping because you did not select a file" Exit Sub Else Workbooks.Open Filename:=newFN End If Windows(newFN).Select Sheets("Sheet4").Move Befo=Workbooks("NewFN").Sheets(1) As you can see, I am selecting sheet 4. Then I open the workbook into which I would like to copy sheet 4. I know how to automate moving a sheet into a static workbook, but I want to be able to change the workbook as needed. Thanks for your help. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy sheet cells into differnt workbook/sheet, How? | Excel Discussion (Misc queries) | |||
| Copy Sheet from One workbook to another EXISTING workbook | Excel Programming | |||
| automatic copy and paste from sheet to sheet in a workbook | Excel Programming | |||
| Copy sheet from one workbook to another workbook problem | Excel Programming | |||
Linear Mode
