Update Sheet but only Columns A:G

Question

I got help with the below code to get data from Columns A,C:G and J from Source Sheet and put it into Columns A to G in Target Sheet depending on 24th column(X) having a 1 in it in that row. It all works fine, my Target Sheet updates with the correct information from the Source Sheet, however it also deletes all data past Column G. For example if I type 100 in Column J in my Target Sheet when I come back to my Target Sheet next time it's gone. I want to keep the data I have in Columns past Column G.

How can i stop the code below from deleting my data in Columns after Column G? I tried a couple things but my excel goes into a fit as I think I'm creating a loop.

Any help is hugely appreciated.

Private Sub Worksheet_Activate()

    Dim Source As Worksheet: Set Source = Sheets("From")
    Dim Target As Worksheet: Set Target = Sheets("To")

    Application.ScreenUpdating = False

    Target.UsedRange.Offset(1).Clear

    With Source.[A1].CurrentRegion
            .AutoFilter 24, 1
            Union(.Columns("A"), .Columns("C:G"), .Columns("J")).Offset(1).Copy
            Target.Columns("A:G").End(3)(2).PasteSpecial xlValues
            .AutoFilter
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

  End Sub

Used Range is an object that represents all the cells that have contained a value at any time. So when you use `Target.UsedRange.Offset(1).Clear` - it will delete all the values in the To sheet. Make sure to specify the region you want to perform delete. [Click here for reference](https://stackoverflow.com/questions/19593907/clear-contents-of-cells-in-vba-using-column-reference) — Dhamo, Jun 13 '20 at 05:31

Update Sheet but only Columns A:G

0 Answers0