Why do I have a runtime error on my code?

Question

1.00/5 (1 vote)

See more:

When I'm running my code I have a runtime error on

Sheets2.UsedRange.Columns.AutoFit

could someone tell me why please?
The reason of this ActiveX button click is to select all cells in sheet 2, autofit the columns and select particular columns to change the font of it.
Thanks in advance.

What I have tried:

Private Sub CommandButton3_Click()

    Worksheets(2).Activate
    Worksheets(2).Range("A1:AZ1000").Select
    Sheets2.UsedRange.Columns.AutoFit
    'Sheet2.Range("A1").Select
    Range("Table1[[#Headers],[ns1:Identification]]").Select
    Selection.AutoFilter
    Range("E:E,G:G,I:I,K:K").Select
    Range("Table1[[#Headers],[ns1:Identification7]]").Activate
    ActiveWindow.SmallScroll ToRight:=29
    Range("E:E,G:G,I:I,K:K,AG:AG,AI:AI").Select
    Range("Table1[[#Headers],[ns1:Identification20]]").Activate
    ActiveWindow.SmallScroll ToRight:=6
    Range("E:E,G:G,I:I,K:K,AG:AG,AI:AI,AM:AM").Select
    Range("Table1[[#Headers],[ns1:MeterIdentification]]").Activate
    Selection.NumberFormat = "0.00"
End Sub

Posted 29-May-17 4:48am

AAB40

Updated 30-May-17 1:25am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-05-29T05:25:00

Solution 1

Are you sure that Sheets2 exists?

I'm pretty sure you have to replace: ~~Sheets2~~ with: Sheet2. As you can see "s" is redundant.

I'd suggest to read this tip/trick: Copy Data Between Excel Sheets using VBA[^] before you start continue your VBA journey. I provided there few mistakes (commonly named as "bad practices") and the way to resolve them.

Posted 29-May-17 5:25am

Maciej Los

Comments

AAB40 29-May-17 12:04pm

thank you Maciej. I will have a look tomorrow ;-)

AAB40 30-May-17 6:44am

Maciej, I have changed my code a bit but now I have a "runtime error 1004: Select method of Range class failed". Your suggestion not to use the 's' didn't work for me. I have edited the code above. Could you have a look please? Know that I first recorded my actions with macro recording and copied-pasted this code in my ActiveX button.

Patrice T 30-May-17 6:53am

Use Improve question to update your question.
So that everyone can pay attention to this information.

AAB40 30-May-17 7:24am

I have deleted the improvement as it gives me another runtime error. the solution maciej gave doesn't work for me. I will open a new thread.

Patrice T 30-May-17 8:01am

you forgot to put this comment in your 'edit'