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.

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.

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.

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.

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.

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.

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.

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.

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

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