Import sheet error

[Guest]

Hi

I am trying to use the below code to import a title page for my reports but
it is coming with the error message:
'Compile error: Object error'
On line 'Set CurrWb = ActiveWorkbook.Name'

Can anyone help resolve this?

TIA

Sub AddTitlePage()
Dim CurrWb As String
Dim TitleWb As Workbook

Set CurrWb = ActiveWorkbook.Name
Set TitleWb = Workbooks.Open("F:\Resources\Title Page.xls")

With TitleWb
..Sheets("Title").Copy Before:=Workbooks(CurrWb).Sheets(1)
..Close
End With

End Sub

 

[Robin Hammond]

Edgar,

As a general rule, unless you are assigning an object, you don't need a Set
statement. i.e.

CurrWb = ActiveWorkbook.Name

But, you could do the whole thing without the use of the String, e.g.

Sub AddTitlePage()
Dim CurrWb As WORKBOOK
Dim TitleWb As Workbook

Set CurrWb = ActiveWorkbook
Set TitleWb = Workbooks.Open("F:\Resources\Title Page.xls")

With TitleWb
..Sheets("Title").Copy Before:=CurrWb.Sheets(1)
..Close
End With

End Sub

Robin Hammond
www.enhanceddatasystems.com