Copying Worksheet to Another Open Workbook

[marston.gould]

I have a worksheet named "Temp" that resides in a Workbook called
"BaseFile.xls". I'm trying to copy it to another workbook whose name I
have stored in a string fStr.

Here's what I have:

Windows("BaseFile.xls").Activate
Sheets("Temp").Copy After:=Workbooks(fStr).Sheets(1)

But I'm getting a subscript out of range problem with fStr in
Workbooks....

Could someone tell me what the correct code should be.
Most appreciated.

 

[Dave Peterson]

I'd use the workbooks collection (instead of windows):

Workbooks("BaseFile.xls").workSheets("Temp").Copy _
After:=Workbooks(fStr).Sheets(1)

But what's in fStr?

If you don't include the .xls, start including it.
If you include the path, don't.

And is the workbook named fStr open?

 

[Norman Jones]

Hi Marston,

Your code should work.

If the two workbooks are open and the Temp sheet exists, you should look at
the possibility that the name of one or other of these may be mis-spelled in
your code - a subtle error such as a missing or added space perhaps?

Once you have resolved this, you could drop the activation, fully qualify
the worksheet name and, as Dave suggested, use the Workbooks collection:


Sub Tester03()
Dim fStr As String
fStr = "MyOtherWorkbook.xls"

Workbooks("BaseFile.xls").Sheets("Temp").Copy _
After:=Workbooks(fStr).Sheets(1)
End Sub