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