Run time error 404 - when trying to copy random columns from one sheet to other

Hi,

We are facing an issue with below piece of code. I am trying to copy random columns from one worksheet to other sheet.

Tried using below code . We get "Object Defined Error" Run time error 404.

Dim Sheet As Range

Set Sheet = Application.Union(Range("Sprint ID"), Range("Requirements"), Range("Status"))

Please let us know your suggestions.

Thanks in Advance

Hi,

Thanks for your reply.

This is the code which I made for finding out a column based on its name across the workbook and paste them to new sheet. The below piece of code is working if we specify the column names explicitly. But by passing as an argument it's throws out an error Run time error 424.


Dim WkSht As Worksheet
Dim r As Integer
Dim test As Range
Dim NewSheet As Worksheet
Dim sName As String
Dim Status As Range
Dim SprintID As Range
Dim Req As Range
Dim Status1 As String
Dim SprintID1 As String
Dim Req1 As String
Set NewSheet = ThisWorkbook.Worksheets.Add
NewSheet.Name = "FCS"
For Each WkSht In ThisWorkbook.Worksheets
sName = WkSht.Name
If sName <> "FCS" Then
For r = 1 To 50
Set Status = WkSht.Cells.Find("Status", , xlValues, xlWhole)
Set SprintID = WkSht.Cells.Find("Sprint ID", , xlValues, xlWhole)
Set Req = WkSht.Cells.Find("Requirements", , xlValues, xlWhole)
If Status Is Nothing Then
Else
Status1 = Left(Status.Address, 2) & r
SprintID1 = Left(SprintID.Address, 2) & r
Req1 = Left(Req.Address, 2) & r
If StrComp(WkSht.Range(Status1).Value, "PostPoned", vbTextCompare) = 0 Then

Set test = Application.Union(Range(Status1), Range(SprintID1), Range(Req1)).Copy
'Line which throws out run time error 424

End If
End If
Next r
End If
Next WkSht
End Sub


Tried out based on your suggestion. Still it throws out the error.

Error.JPG


Hi,


Kindly find the attached screenshot. I guess i am making use of correct range. Kindly advice.