Run-time error '1004': Method 'Range' of object '_Global' failed

1
votes

I'm trying to copy a table from one worksheet to another in the same workbook, and to name it "Table2", but I keep getting the error code "Run-time error '1004': Method 'Range' of object '_Global' failed".

VB:

With Sheets("Sheet1")
    .Range("table1[[#all], [#all]]").Copy Destination:=Sheets("Results").Range("A1")
End With

With Sheets("Results")
    Set copyData = .Range("a1").CurrentRegion 'sets range to that containing data
    .ListObjects.Add(xlSrcRange, Range(copyData), , xlYes).Name = "Table2" 'HERE IS THE PROBLEM LINE
End With

Really struggling with this, any help much appreciated.

1
excelosx-mountain-lionvba

1 Answers

0
votes

You can access the table by using the index number.

With Sheets("Sheet1")
    .Range("table1[[#all], [#all]]").Copy Destination:=Sheets("Results").Range("A1")
End With

With Sheets("Results")
    Set copyData = .Range("a1").CurrentRegion 'sets range to that containing data
      .ListObjects(1).Name = "Table2"
End With