Copy Sheet into another workbook

[Brennan]

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 Before:=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.

 

[Joel]

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

 

[Jim Thomlinson]

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 Before:=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 Before:=Workbooks("NewFN").Sheets(1)

 

[Jim Thomlinson]

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.

 

[Joel]

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 Before:=Workbooks("NewFN").Sheets(1)
to
Sheets("Sheet4").Move Before:=Workbooks(NewFN).Sheets(1)

You also need to remove the pathname from NewFN.

 

[Jim Thomlinson]

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 Before:=wbkOpen.Sheets(1)

 

[Brennan]

I made the change that you suggested and that particular line now errors out.
Any suggestions. Thanks again for the help.

 

[Brennan]

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

 

[Dave Peterson]

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 _
before:=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 Before:=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 Before:=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").activate

I avoided the trouble by using a workbook variable that would represent that
newly opened workbook.

pps. Untested, uncompiled. Watch for typos!

 

[Brennan]

Thanks Dave

Your code worked perfectly.

Thanks to Jim and Joel as well.

B