Unable to copy to different sheet

Question

I tried in different ways to resolve this excel issue but didn't find any resolution. Hope someone out here will be able to resolve my problem. I have a spreadsheet consists of 20+ tabs and each tab is pulling data from different websites and all these data are consistent in format. I am trying to archive the data in another sheet named "Archive" when the source data changes.

Here is the VBA code I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Target.Column = 3 And UCase(Target) = "2016." Then
        Cells(Target.Row, Target.Column).EntireRow.Copy _
        Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If

    Application.EnableEvents = True
End Sub

I would really appreciate if someone helps me out here.

Answer 1

See This Answer where I describe how to make a Query Table responsive to events.

Relevant details quoted below, insted of the MsgBox you'll need to write some code to copy all of the data (e.g., using the ResultRange.Address property of the query table), if you need to copy the entire table to archive, then it would be like:

Me.cQT.ResultRange.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)

If you need to conditionally loop/copy rows based on criteria, you'll need to write that code, e.g.:

Dim rng as Range
For each rng in Me.cQT.ResultRange.Rows
    If rng.Cells(3).Value = "2016." Then
        rng.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next

(Or something like that, I'm not exactly sure what logic you need to implement)

How to add Event Handler for QueryTable

Create a Class module named clsEvents_QueryTable (or you can name it something else, just be consistent in the rest of the code). Put this code, which will allow you to establish an event-handler for a QueryTable object, and the two events' procedures.

Option Explicit
Public WithEvents cQT As Excel.QueryTable

Private Sub Class_Initialize()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)   '## Modify as needed
    
    Set cQT = ws.QueryTables.Item(1)  '## Modify as needed
End Sub

Private Sub cQT_AfterRefresh(ByVal Success As Boolean)
    '###
    '   Code placed in, or called *from* this procedrure will run AFTER refresh
    MsgBox Me.cQT.Name & " has been refreshed"
    
End Sub

Private Sub cQT_BeforeRefresh(Cancel As Boolean)
    '###
    '   Code placed in, or called *from* this procedrure will run BEFORE refresh
    MsgBox Me.cQT.Name & " Archiving Before refreshing..."
    Me.cQT.ResultRange.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

Put this in the top of a standard module:

Public QT As clsEvents_QueryTable

In your ThisWorkbook module, do this:

Option Explicit

Private Sub Workbook_Open()
    If QT Is Nothing Then
        Set QT = New clsEvents_QueryTable
    End If
End Sub

(You could do that in some other module, but this is just an example).

Now the table has the two event-handlers, and any time the QueryTable is refreshed, it will automatically invoke the code which is included or called from the event handler(s).

You can extend this to handle multiple QueryTables with some modification (using a collection of object, instead, etc.).