0

I am attempting to write VBA code that will copy only the table on the sheet and past it into another sheet. I am getting a Run-time error '1004': Method 'Range' of object'_Global' failed. My guess is that I do not have one or more of the references correct. What am I doing wrong? Any help is much appreciated.

This is the code:

Sub Final_Report()

' Final_Report Macro

   Sheets("Main Table(Atlas)").Select
    Dim rngData As Range
    Dim intRow As Integer
    Dim intColumn As Integer

        intRow = Range("AtlasReport_1_Table_1!11").Row
        intColumn = Range("AtlasReport_1_Table_1!1").Column

        Set rngData = Worksheets("AtlasReport_1_Table_1").UsedRange
        rngData.Offset(intRow - 1, intColumn - 1).Resize(rngData.Rows.Count - intRow + 1, rngData.Columns.Count - intColumn + 1).Select
 
    Selection.Copy
    Sheets("Final").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub

The debugger is pointing to this line:

intRow = Range("AtlasReport_1_Table_1!11").Row
BigBen
  • 46,229
  • 7
  • 24
  • 40
Tony_T
  • 1
  • Some guidance here on working with various ListObject-related ranges: https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables#:~:text=password%20protected%20sheet.-,Selecting%20Areas%20of%20a%20Table%20with%20VBA,-Select – Tim Williams Jan 12 '23 at 17:03
  • Another tip: Read [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424). – Ike Jan 12 '23 at 17:19

1 Answers1

1

You could do something like this (no copy/paste, just direct assignment using .Value)

Sub Final_Report()
    Dim wb As Workbook
    
    Set wb = ThisWorkbook
    With wb.Worksheets("Main Table(Atlas)").ListObjects("AtlasReport_1_Table_1").Range
        wb.Worksheets("Final").Range("B1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125