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