0

Hi i have error "Cannot complete oreation: A table cannot overlap with a PivotTable report, query results, a table, merged cells, or an XML mapping" What does it mean it was working till today and now it is broked. Please help me.

  Sub CopyCurrentRegion()
Application.ScreenUpdating = False
Application.EnableEvents = False

Application.Calculation = xlCalculationManual
    Sheets("owssvr").Range("Table_owssvr").ListObject.QueryTable.Refresh BackgroundQuery:=False 'refresh and copy data base to the main view
    Range("Table_owssvr[#All]").Copy Sheets("NA").Range("A6")
    Sheets("NA").Columns("A:AC").EntireColumn.AutoFit

Dim c As Range

    For Each c In Range("A5:ae5").Cells
        If c.VALUE = "y" Then
            c.EntireColumn.Hidden = True     'hide all  columns where in range a5 to ae5 we have IN CELLS value x (maybe we will need this column). If u wana unhide this columns then  push other button :)
        End If
    Next c

If Weekday(Now()) = vbMonday Then
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=12, _
    Criteria1:=">=" & CLng(Date) - 4, Operator:=xlAnd, Criteria2:="<=" & CLng(Date)
Else
If Weekday(Now()) = vbTuesday Then
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=12, _
    Criteria1:=">=" & CLng(Date) - 4, Operator:=xlAnd, Criteria2:="<=" & CLng(Date)       ' filter fresh date(last date cut 24 h)
Else
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=12, _
    Criteria1:=">=" & CLng(Date) - 2, Operator:=xlAnd, Criteria2:="<=" & CLng(Date)
End If
End If
With Sheets("NA")
        .ListObjects(1).Name = "europe"
End With
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Salim
  • 11
  • 1
  • 5
  • I suspect your table column names have changed or there is an extra column. Excel would then require it to grow to deal with the change, and if there is a table etc. to the right already, then it can't do that without affecting that table. – Joe Mar 23 '21 at 12:21
  • Should i just delete before copy and copy and paste this table ? – Salim Mar 23 '21 at 13:40
  • It's up to you really. It's best not having anything to the right of a table if the columns are dynamic, or are likely to change. If it's a one-off, then you can cut and paste the table somewhere lower down where there is nothing to the right of it, do the refresh and then put it back to where it was - save your work first though because in the past, the links have randomly disappeared on occasion - not happened for a while though. – Joe Mar 23 '21 at 13:42
  • 1
    Thanks man i find solution its very stupid that excel cant do automaticly. – Salim Mar 24 '21 at 12:14

0 Answers0