Copy method of worksheet class failed (Run-time Error 1004)

[Jamaal09]

Hey guys,

Im using Excel 2013 and I'm getting an issue in vba I can't figure out. (This is something I've done several dozen time before) But everytime I try to copy a sheet in a workbook,

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sheets("Sheet1").Copy After:=WB.Sheets(WB.Sheets.Count)</code></pre>
I recently copied in this sheet from another workbook, and deleted all of it's formula names, but I can't copy any other sheets now either.
the Run time Error 1004: Copy method of Worksheet Class failed pops up. What is weird is that I put in a msgbox and

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">MsgBox (WB.Sheets.Count)</code></pre>
returns a "1" though the sheet has about a dozen sheets within it. Anything I'm missing? Ive saved the workbook and even saved it as another name.


the sheet I imported has a sheet number of 77 while the previous last sheet was 23, could this be a cause?

 

[Jamaal09]

Update: Fixed, it looks like when I was setting WB = thisworkbook and a few other methods it was refering to my Personal workbook. I changed it to activeworkbook and its working now. No clue why it did that. Any thoughts?

 

[Jerry Sullivan]

Hi Jamaal09,

ThisWorkBook refers to the workbook in which the code making that reference is stored.

So for code stored in your Personal.xlsb file, ThisWorkbook.Sheets.Count will return the number of Sheets in Personal.xlsb - not the number of Sheets in the ActiveWorkbook.