VBA, Defining the current workbook as a variable

[aallaman]

Hi All,

I am writing a macro that starts in one workbook (who's file name will change weekly) and will also access a reference sheet on the network. The goal is to copy the reference sheet into the first workbook.

In order to do this I'm using the following code, but I am not getting past the Set CurMP line. Am I defining that variable incorrectly?

Thanks for your help!!!

Allen

Dim CurMP As Workbook
Set CurMP = Workbooks(ThisWorkbook)
Workbooks.Open Filename:= _
"G:\Ad Services\Analytics\FY11 Campaigns\FY11 Windows\Allen's Stuff\BigBrother\BigBrotherRefSheet.xlsx"
Sheets("Ref Sheet").Copy After:=Workbooks(CurMP).Sheets(1)

 

[GK039]

Maybe this:

Set CurMP = Workbooks("ThisWorkbook")

Hi All,

I am writing a macro that starts in one workbook (who's file name will change weekly) and will also access a reference sheet on the network. The goal is to copy the reference sheet into the first workbook.

In order to do this I'm using the following code, but I am not getting past the Set CurMP line. Am I defining that variable incorrectly?

Thanks for your help!!!

Allen

Dim CurMP As Workbook
Set CurMP = Workbooks(ThisWorkbook)
Workbooks.Open Filename:= _
"G:\Ad Services\Analytics\FY11 Campaigns\FY11 Windows\Allen's Stuff\BigBrother\BigBrotherRefSheet.xlsx"
Sheets("Ref Sheet").Copy After:=Workbooks(CurMP).Sheets(1)

 

[aallaman]

If I leave the quotation marks out I get a "Type Mismatch" error

If I put them in then I get a "Subscript out of range" error

 

[Norie]

Try this.

Set CurMP = ThisWorkbook

 

[aallaman]

Thanks everyone for your help!

@Norie - That gets me through setting the variable

Now I am getting a "Type Mismatch" error on this line.



Dim CurMP As Workbook
Set CurMP = ThisWorkbook
Workbooks.Open Filename:= _
"G:\Ad Services\Analytics\FY11 Campaigns\FY11 Windows\Allen's Stuff\BigBrother\BigBrotherRefSheet.xlsx"
Sheets("Ref Sheet").Copy After:=Workbooks(CurMP).Sheets(1)





I had this working the other day until I accidentally erased the macro. I cannot remember for the life of me how I did it.

 

[Norie]

You don't need Workbooks - CurWB is a reference to ThisWorkbook.

It can just be used on it's own.

Something like this.

Set wbRef = Workbooks.Open (Filename:= _
"G:\Ad Services\Analytics\FY11 Campaigns\FY11 Windows\Allen's Stuff\BigBrother\BigBrotherRefSheet.xlsx")
 
wbRef.Sheets("Ref Sheet").Copy After:=CurMP.Sheets(1)

PS Hope you don't mind but I added a bit so you have a reference to the workbook getting opened too.

 

[aallaman]

@Norie

That gives me a "Copy method of Worksheet class failed" error

 

[aallaman]

whoops I missed your wbRef addition, once I put that in I get an "object required" error

 

[Norie]

What exact code do you have now?

Did you include this line?

Set wbRef = Workbooks.Open (Filename:= _
"G:\Ad Services\Analytics\FY11 Campaigns\FY11 Windows\Allen's Stuff\BigBrother\BigBrotherRefSheet.xlsx")

If you didn't that might explain the 'object required' error.

It creates a reference, wbRef, to the workbook you are opening.

While it's not strictly necessary to do that it could make things a lot easier.

For example if you want to close the workboook after you've copied the worksheet from it:

wbRef.Close

 

[aallaman]

Here is my exact code, I was missing the Set wbRef = statement.

Now I am back to getting a ""Copy method of Worksheet class failed" error

Dim CurMP As Workbook
Set CurMP = ThisWorkbook
Set wbRef = Workbooks.Open(Filename:= _
"G:\Ad Services\Analytics\FY11 Campaigns\FY11 Windows\Allen's Stuff\BigBrother\BigBrotherRefSheet.xlsx")
wbRef.Sheets("Ref Sheet").Copy After:=CurMP.Sheets(1)