Define workbook name as variable

[sweeneytime]

Hi,

I wish to set up a macro in a workbook - reports. This will copy info from another wb many times - results. The name of the reports wb will change. Therefore I wish to declare the wb name when I run the macro.

I have tried the following.

The workbook name is returned in D3.

Dim Reports As Workbook
    
 Reports = Sheets("Conserv").Range("D3").Value

&

Reports = activeWorkbook.name

Would someone be able to suggest a solution please?

Thanks,
Alan

 

[Sektor]

Dim Reports As Workbook
Set Reports = Sheets(Sheets("Conserv").Range("D3").Value)

 

[sweeneytime]

Thanks for that, but I get a "run time error 9" now.

I have used a formula to return the address in the wb so I though it would be correct. I double checked the sheet reference too in the complier.

 

[Sektor]

I am very sorry. Instead of Sheets, I should write Workbooks. Here's correction:

Dim Reports As Workbook
Set Reports = Workbooks(Sheets("Conserv").Range("D3").Value)

 

[sweeneytime]

I'm still getting the same error. Thanks anayway