VBA - Run Time Error 1004 'Application Defined or Object Defined'

Question

I want to copy a range of table and paste it in a new workbook. I want to copy ranges composed of other columns and rows but as variable. For example, I copy columns C and E with the rows from 1 to 17, 20, from 32 to 39 and 45. And I want to proceed that maximum 10 times (for the columns) and if I finish selecting the columns to copy, I exit. To be more clear, there is an example: I select column B, D, F and than select the rows to be copied (they are the same for all the columns) and finally copy them and paste them in a new workbook. So I stopped after three times and copy what I selected and go out.

My Code:

Sub Macro33()
Dim col As String, row As String
'Dim row As Integer
Dim CopyRange As Range
Set CopyRange = [A:A]

  For i = 1 To 10
    col = InputBox("Column number " & i & ", if finish write 'done'")
    If col = "done" Then
      MsgBox ("copy finished")
      GoTo ExitIteration
    Else
      row = InputBox("row number to copy (max to 62), if finish write 'done'")
      Do While row <> "done"
        Set CopyRange = Union(CopyRange, Range(col & row & ":" & col & row))
        row = InputBox("row number to copy (max to 62), if finish write 'done'")
      Loop
    End If
  Next

 ExitIteration:
   CopyRange.copy
   Workbooks.Add
   ActiveSheet.Paste
   Windows("Pedro.xlsm").Activate
 End Sub

The problem is in the Line:

  Set CopyRange = Union(CopyRange, Range(col & row & ":" & col & row))

I always get application-defined or object-defined error (error 1004) and can't find any solution for it!! Any help please?

What are the values of col and row when the error raises? I think it looks like you're assigning these long/integer values, and that will result in an invalid range address string (col & row & ":" & col & row) — David Zemens
Are you sure that's the correct error line? I get a 1004 on CopyRange.Copy because it can't be used on multiple selections. Also, row and col are always the same, so you'll end up with ranges like "B2:B2". — Comintern

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2016-11-30T13:44:40

Union can only join two or more ranges. On your first pass CopyRange will be nothing so the union method will fail.

Try something like:

If copyrange Is Nothing Then
    Set copyrange = Range(col & Row & ":" & col & Row)
Else
    Set copyrange = Union(copyrange, Range(col & Row & ":" & col & Row))
End If

VBA - Run Time Error 1004 'Application Defined or Object Defined'

1 Answers