Code bugs on simple copy and paste

  • Hi everyone, I have a (hopefully) simple problem - I'm trying to copy and paste a number of columns in my file. The code I have works just fine if the sheet I'm trying to change is the active sheet; however, when another sheet is activated I get the "Application-defined or object-defined error" message on the following line:

    Sheets(ws.Name).Range(Columns(1), Columns(2)).Copy

    My research shows that the problem with the code might be that .Cells property (and I'm assuming .Columns works the same way) by default references the active sheet. The suggested solution has been to use "With ws" statement similar to below:

    With ws
         .Range(Columns(1), Columns(2)).Copy
    End With

    However, this causes the code to bug out as well. Any suggestions on how I can improve my code to make it work without activating the sheet where I'm doing the copying and pasting?

    Thanks in advance!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!