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 Before:=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 Before:=Workbooks("test.xls").Sheets(1)
'Or do you mean
Thisworkbook.Sheets("database").Copy Before:=WB.Sheets(1)
'Or maybe, depending on which workbook contains the sheet "database"
WB.Sheets("database").Copy Before:=Thisworkbook.Sheets(1)
wb.close

NickHK

"Gert-Jan" <(E-Mail Removed)> wrote in message
news:4573f32d$0$2318$(E-Mail Removed)...
> 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 Before:=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
Before:=Workbooks("test.xls").Sheets(1)

NickHK

"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
Before:=Workbooks("test.xls").Sheets(1)
> 'Or do you mean
> Thisworkbook.Sheets("database").Copy Before:=WB.Sheets(1)
> 'Or maybe, depending on which workbook contains the sheet "database"
> WB.Sheets("database").Copy Before:=Thisworkbook.Sheets(1)
> wb.close
>
> NickHK
>
> "Gert-Jan" <(E-Mail Removed)> wrote in message
> news:4573f32d$0$2318$(E-Mail Removed)...
> > 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 Before:=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" <(E-Mail Removed)> schreef in bericht
news:%(E-Mail Removed)...
> 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
> Before:=Workbooks("test.xls").Sheets(1)
>
> NickHK
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
> Before:=Workbooks("test.xls").Sheets(1)
>> 'Or do you mean
>> Thisworkbook.Sheets("database").Copy Before:=WB.Sheets(1)
>> 'Or maybe, depending on which workbook contains the sheet "database"
>> WB.Sheets("database").Copy Before:=Thisworkbook.Sheets(1)
>> wb.close
>>
>> NickHK
>>
>> "Gert-Jan" <(E-Mail Removed)> wrote in message
>> news:4573f32d$0$2318$(E-Mail Removed)...
>> > 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 Before:=Workbooks("test.xls"). _
>> > Sheets(1)
>> > Workbooks.Close Filename:=filex
>> > End Sub
>> >
>> > The error is in the last row.
>> >
>> > Thanks, Gert-Jan
>> >
>> >
>>
>>
>
>