title: Finding the address of a table range using vba
body: <p>I am working with an excel sheet that has a bunch of sheets with data in tables. I am trying to consolidate the sheets. I do not want the copied data to be in tables. I am able to find the tables range address for all the sheets except one, which is retunring an address of $1:$104. All the other ranged are like this "$A$1:$J$43" . When I try to copy this table using the address it returns , I get the runtime error "1004". For now , the code rewrite all the tables in the same place,  but I will be changing the code to copy the tables into different places in the destination sheet. 
  Here is my code:</p>
  
  <pre><code>  Sub mergeWorksheets()
     Dim wrk As Workbook 'Workbook object - Always good to work with 
   object variables
     Dim sht As Worksheet 'Object for handling worksheets in loop
     Dim trg As Worksheet 'Master Worksheet
     Dim rng As Range 'Range object
     Dim colCount As Integer 'Column count in tables in the worksheets
     Dim mLastRow As Integer
     Dim LastRow As Integer
     Dim rngFound As Range
     Dim i As Integer
  
  Set wrk = ActiveWorkbook 'Working in active workbook
  
  'We don't want screen updating
  Application.ScreenUpdating = False
  
  ' would rather not do a loop but using a function to check and delete sheet renders error
  For Each Sheet In ActiveWorkbook.Worksheets
   If Sheet.Name = "Master" Then
      Application.DisplayAlerts = False
      Sheets("Master").Delete
      Application.DisplayAlerts = True
   End If
  Next Sheet
   ' Add new worksheet as the last worksheet
    Set trg = wrk.Worksheets.Add(Before:=wrk.Worksheets(1))
   ' Rename the new worksheet
    trg.Name = "Master"
  
   'We can start loop
   For Each sht In wrk.Worksheets
      'If worksheet in loop is the last one, stop execution (it is Master worksheet)
      If sht.Name Like "*Attri*" Then
             Debug.Print sht.Name
             'Find the last row of the master sheet
             Set rngFound = trg.UsedRange.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)
             If Not rngFound Is Nothing Then
             'you found the value - do something
                 mLastRow = rngFound.Row
                 Debug.Print "Last row of master " &amp; rngFound.Address, mLastRow
             Else
             ' you didn't find anything becasue sheet is empty - first pass
                mLastRow = 0
             End If
             For Each tbl In sht.ListObjects
                 'Do something to all the tables...
                  Debug.Print tbl.Name
                  Debug.Print tbl.Range.Address
                  'Put data into the Master worksheet
                      tbl.Range.Copy Destination:=trg.Range("B1")
                   Next tbl
  
         '    trg.Cells(mLastRow + 1, 1).Value = "Tab Name"
          '   trg.Cells(mLastRow + 1, 1).Font.Bold = "True"
           '  trg.Range("A" &amp; mLastRow + 1).Value = sht.Name
  
          Debug.Print "-------"
          Else
             ' Debug.Print "error " &amp; sht.Name &amp; " is missing header "
          End If
  
  Next sht
  </code></pre>