Hi
question remains: Dou you want to copy formats as well or are you only
interested in the values.
Also: why can't you open the other workbook: would be faster and easier
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

"mango" schrieb im Newsbeitrag
...
dear frank,
i do not get you.
may i know if you can modify my script below? let say just copy a
range
a1:ff100
thanks alot


"Frank Kabel" wrote:

Hi
if you really want to copy a complete sheet you have to open the
other file
(you8 can hide this operation though with
application.screenupdating=false).

"mango" wrote:

dear all, i want to copy a sheet in a closed workbook to one of
the sheet in
an active workbook. i have the following scripts but i have the
problem
1) cannot copy over
2) the vba unable to perform automatically which means there is a
dialog box
asking to input the workbook name though already mentioned in the
script.
pls help to modify so that can automatically copy to sheet2.
button is
sitting in sheet1 in active workbook.

Private Sub CommandButton7_Click()
Cells.ClearContents
p = "\\Server_app\Budget\PC\"
f = "Backupofbrm-2004-master.xls"
s = "CTC"
a = "A1"
Range("a1").Value = GetValue(p, f, s, a)
End Sub

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Create the argument

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function