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

 

[Trevor G]

Welcome to the forum

You have misspelled look at the following

Dim crwb As Workbook

crbw.Worksheets

Should be

crwb.Worksheets

 

[VoG]

Try

crwb.Select
Worksheets("Review").Select
Rows(y).Select

 

[VBA n00b]

Ok, I figured otu the 424 error now I'm getting:

Run-time error '1004'
Application-defined or Object-defined error

 

[VBA n00b]

with:

For x = 1 To 3000
If Not (IsEmpty(wb.Worksheets("report").Cells(x, 1))) Then
wb.Worksheets("report").Rows(x).Select
Selection.Copy
crwb.Select
Worksheets("Review").Select
Rows.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

y = y + 1

End If
Next x


i get:

Run-time error '438'

Object doesn't support this property or method

 

[marzvolta]

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!

 

[VBA n00b]

So i did some simple debugging and i did a Msgbox debug...

with this:

For x = 1 To 3000
If Not (IsEmpty(wb.Worksheets("report").Cells(x, 1))) Then
wb.Worksheets("report").Rows(x).Select
Selection.Copy

MsgBox "The row is" & y
crwb.Select

Worksheets("Review").Select
Rows.Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

y = y + 1

End If
Next x


a 424 error occurs right after the msgbox

and when i put the msgbox right after

crwb.select it dose not get to the msgbox before the 424 error occurs

Anyone know why crwb.select throws an error?

 

[VBA n00b]

Another quick thing is that if I run the code without have the workbook that I'm copying the rows from i get error '438' but if I run the code with the workbook already open I get error code '424'

 

[Norie]

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

 

[taurean]

First save a backup copy and then test the following code:

Dim Filepath As String
Dim x As Integer
Dim y As Integer
Dim wb As Workbook
Dim crwb As Workbook
 
Private Sub Test()
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 wb.Worksheets("report").Range("A" & x).Value2 = "" Then
    wb.Worksheets("report").Rows(x).Copy
    crwb.Worksheets("Review").Rows(y).PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    y = y + 1
    End If
Next x
wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
End Sub