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)

 

[Norie]

The code works fine for me.

Are you sure you 'Ref Sheet' is right?

Or perhaps the workbook you are trying to copy the worksheet into is protected.

 

[aallaman]

Hi Noire,

Thanks a lot for your help. Unfortunately the code still doesn't work on my end. I confirmed the Ref Sheet name and verified that there are no protections on either workbook but still no luck. I'll keep poking at it and post a reply if I can figure out why it isn't working.

Allen

This is how my code stands for now

'
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)

 

[RSXchin]

try

Set CurMP = Application.ThisWorkbook

 

[aallaman]

@rsxchin- still no luck, getting the same "Copy method of Worksheet class failed" error


<!-- / message -->

 

[Norie]

Are there any chart sheets in either workbook?

That's about the last thing I can think of.

I can't even recreate the error you are getting.