Hi,

Can anyone help to make this work:

Sub CopyAndClose()
Dim filex
filex = Range("Sheets2!F2").Value
Workbooks.Open Filename:=filex, Password:="123"
Sheets("database").Select
Sheets("database").Copy Befo=Workbooks("test.xls"). _
Sheets(1)
Workbooks.Close Filename:=filex
End Sub

The error is in the last row.

Thanks, Gert-Jan

You will find it more easy to give yourself some variables to use.
dim WB as workbook

set wb=Workbooks.Open Filename:=filex, Password:="123"

'Don't what Workbooks("test.xls") refers to ?
Thisworkbook.Sheets("database").Copy Befo=Workbooks("test.xls").Sheets(1)
'Or do you mean
Thisworkbook.Sheets("database").Copy Befo=WB.Sheets(1)
'Or maybe, depending on which workbook contains the sheet "database"
WB.Sheets("database").Copy Befo=Thisworkbook.Sheets(1)
wb.close

NickHK

"Gert-Jan" wrote in message
...
Hi,

Can anyone help to make this work:

Sub CopyAndClose()
Dim filex
filex = Range("Sheets2!F2").Value
Workbooks.Open Filename:=filex, Password:="123"
Sheets("database").Select
Sheets("database").Copy Befo=Workbooks("test.xls"). _
Sheets(1)
Workbooks.Close Filename:=filex
End Sub

The error is in the last row.

Thanks, Gert-Jan

That should read :
set wb=Workbooks.Open(Filename:=filex, Password:="123")

and
'Don't know what Workbooks("test.xls") refers to ?
Thisworkbook.Sheets("database").Copy
Befo=Workbooks("test.xls").Sheets(1)

NickHK

"NickHK" wrote in message
...
You will find it more easy to give yourself some variables to use.
dim WB as workbook

set wb=Workbooks.Open Filename:=filex, Password:="123"

'Don't what Workbooks("test.xls") refers to ?
Thisworkbook.Sheets("database").Copy
Befo=Workbooks("test.xls").Sheets(1)
'Or do you mean
Thisworkbook.Sheets("database").Copy Befo=WB.Sheets(1)
'Or maybe, depending on which workbook contains the sheet "database"
WB.Sheets("database").Copy Befo=Thisworkbook.Sheets(1)
wb.close

NickHK

"Gert-Jan" wrote in message
...
Hi,

Can anyone help to make this work:

Sub CopyAndClose()
Dim filex
filex = Range("Sheets2!F2").Value
Workbooks.Open Filename:=filex, Password:="123"
Sheets("database").Select
Sheets("database").Copy Befo=Workbooks("test.xls"). _
Sheets(1)
Workbooks.Close Filename:=filex
End Sub

The error is in the last row.

Thanks, Gert-Jan

Hi Nick,

You have given the right solution, thanks a lot.

Best regards, Gert-Jan

"NickHK" schreef in bericht
...
That should read :
set wb=Workbooks.Open(Filename:=filex, Password:="123")

and
'Don't know what Workbooks("test.xls") refers to ?
Thisworkbook.Sheets("database").Copy
Befo=Workbooks("test.xls").Sheets(1)

NickHK

"NickHK" wrote in message
...
You will find it more easy to give yourself some variables to use.
dim WB as workbook

set wb=Workbooks.Open Filename:=filex, Password:="123"

'Don't what Workbooks("test.xls") refers to ?
Thisworkbook.Sheets("database").Copy
Befo=Workbooks("test.xls").Sheets(1)
'Or do you mean
Thisworkbook.Sheets("database").Copy Befo=WB.Sheets(1)
'Or maybe, depending on which workbook contains the sheet "database"
WB.Sheets("database").Copy Befo=Thisworkbook.Sheets(1)
wb.close

NickHK

"Gert-Jan" wrote in message
...
Hi,

Can anyone help to make this work:

Sub CopyAndClose()
Dim filex
filex = Range("Sheets2!F2").Value
Workbooks.Open Filename:=filex, Password:="123"
Sheets("database").Select
Sheets("database").Copy Befo=Workbooks("test.xls"). _
Sheets(1)
Workbooks.Close Filename:=filex
End Sub

The error is in the last row.

Thanks, Gert-Jan