• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

My VBA works, but I keep getting an 'Object Required' error anyway

PipBoy808

Member
Code:
Sub CreateCopy3()
    Set Workbook= ActiveWorkbook
    Sheets([{"Weekly Planning", "Contact Details"}]).Copy.Move
End Sub

The above code copies the first two sheets of a workbook and places them in a brand new workbook. It works, but I keep getting a "Run-time 424: Object Required Error" on the last line nonetheless. This could be a problem as I want to place this sub within another one, and this error may cause the whole thing to break. Does anyone know what the error could refer to?
 
Hi ,

Certainly Excel is functioning well !

Do you wish to copy or to move ? You cannot have both.

And after you have copied , do you wish to save or .... ?

Creating a new subroutine with just two lines is not justified , especially if this procedure will not be called repeatedly. You might as well include these same two lines ( of course after correcting the .Copy.Move part ) in your main procedure.

Narayan
 
Copy and then move the copies while leaving the originals, and it works! It copies those two worksheets and moves them to a new workbook. After I've copied I wish to save as a filepath. I actually have this in my code afterwards:

Code:
ThisWorkbook.SaveAs "C:\Myfilepath\Myfile.xls"

but I don't get that far because I get this error. Again, I'm not sure why I get the error, because the code copies and moves the sheets just fine.

Would something like this work better?

Code:
  Sheets([{"Weekly Planning", "Contact Details"}]).Copy
 
Then
 
  Sheets([{"Weekly Planning", "Contact Details"}]).Move
 
OK weird, I took out the word 'Move' and it works fine without the error. I didn't realise that 'Copy' would be sufficient on its own.
 
Ah wait, I have one more problem. How do I refer to the new workbook and SaveAs in VBA? Using ThisWorkbook.Saveas only saves the original workbook. I guess I have to activate the new workbook somehow maybe?
 
After you do the Copy, assuming you haven't specified a location within ThisWorkbook (like you were copyinig the worksheet within the same workbook), XL will automatically create a new workbook with those sheets, as you've seen so far. To refer to that workbook, it will be the ActiveWorkbook after the copy command.
 
Back
Top