Run-time error '424' Object required

[VBA n00b]

The error occurs at the highlighted line:


Dim Filepath As String
Dim x As Integer
Dim y As Integer
Dim wb As Workbook
Dim crwb As Workbook


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
y = 3
x = 1

Set crwb = ActiveWorkbook

Filepath = Application.GetOpenFilename
If File = "False" Then Exit Sub
MsgBox Filepath

Set wb = Workbooks.Open(Filepath, True, True)

For x = 1 To 3000
If Not (IsEmpty(wb.Worksheets("report").Cells(x, 1))) Then
wb.Worksheets("report").Rows(x).Select
Selection.Copy
<SPAN style="BACKGROUND-COLOR: #ffff00">crbw.Worksheets("Review").Rows.Select</SPAN>
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

y = y + 1

End If
Next x

wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True

End Sub

 

[VBA n00b]

Try getting rid of all the Select/Selection, it's not really needed and can cause problems.

You might also want to check your variable names - I think some of them are wrong so I've changed them.

Check yourself though, if you add Option Explicit at the top of the module that might help you.

Option Explicit
 
Private Sub CommandButton1_Click()
 Dim Filepath As String
Dim x As Integer
Dim y As Integer
Dim wb As Workbook
Dim crwb As Workbook
 
 
    Application.ScreenUpdating = False
 
    y = 3
 
    Set crwb = ThisWorkbook ' this creates a reference to the workbook the code is in
 
    Filepath = Application.GetOpenFilename
 
    If Filepath = "False" Then Exit Sub
 
    MsgBox Filepath
 
    Set wb = Workbooks.Open(Filepath, True, True)
 
    For x = 1 To 3000
 
        If Not (IsEmpty(wb.Worksheets("report").Cells(x, 1))) Then
 
            wb.Worksheets("report").Rows(x).Copy
 
            crwb.Worksheets("Review").Rows(y).PasteSpecial Paste:=xlValues
 
            y = y + 1
 
        End If
 
    Next x
 
    wb.Close False
 
    Set wb = Nothing
 
    Application.ScreenUpdating = True
 
End Sub

thanks Norie! that ended up working!

 

[Trevor G]

Hello,

I'm having some trouble with defining xlSheetVeryHidden properties.

Here's the code


Private Sub Workbook_Open()
Dim sh As Worksheet

For Each sh In Sheets
sh.Visible = xlSheetVisible
MsgBox "Sheet " & sh.Name & " is " & sh.Visible
Next sh

Select Case Environ("UserName")
Case "jmanuels"
TesteB.Visible = xlSheetVeryHidden
TesteC.Visible = xlSheetVeryHidden
Case "User1", "User2"
' Sheet2.Visible = xlSheetVeryHidden
' Sheet1.Visible = xlSheetVeryHidden
Case Else:
MsgBox "Error"
End Select
End Sub


Run-time error 424

Help Needed!

Welcome to the Forum,

In your sample you haven't declared the TesteB or TesteC so you would need to place in the sheet names, also when using the Environ you will have to make sure your username is in the correct case.

I have just tested this and it works OK

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sh <SPAN style="color:#00007F">In</SPAN> Worksheets<br>    sh.Visible = xlSheetVisible<br><SPAN style="color:#00007F">Next</SPAN><br><br><br><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Environ("UserName")<br><SPAN style="color:#00007F">Case</SPAN> "Trevor G"<br>Sheets("sheet1").Visible = xlSheetVeryHidden<br><br><SPAN style="color:#00007F">Case</SPAN> Else:<br>MsgBox "Error"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[marzvolta]

Thank you for your Help! Meanwhile I realized that error and resumed to the same solution.

Regards

 

[Trevor G]

Pleased to read you spotted it and resolved it to give you a working solution. Have a good weekend