Excel VBA - copy and paste changing values from one worksheet to another

[Ceslior]

Hey, I'm new to VBA and this forum and wanted to ask for help.

I have two worksheets; "Report" and "Total".
The goal is to save the values of my table in my "Total" sheet based on the calendar week in cell "S8" as well as 'load' the data as soon as I select the calendar week in cell "S8"

Hopefully there is someone who can adjust my code, so it's not flashing every time I change a value.

Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("C2:D8")) Is Nothing Then
    
    Range("C4:D4").Copy
    CW = Sheets("Report").Range("S8")
    
    Sheets("Total").Select
    Sheets("Total").Range("1:1").Select
    Selection.Find(What:=CW, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    Sheets("Report").Select
    End If
    
    If Not Intersect(Target, Range("S8")) Is Nothing Then
    CW = Sheets("Report").Range("S8")
    
    Sheets("Total").Select
    Sheets("Total").Range("1:1").Select
    Selection.Find(What:=CW, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(, 1).Resize(1, 2).Copy
    Sheets("Report").Select
    Sheets("Report").Range("C2:D8").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    End If
End Sub

 

[QuestionBaker]

Can you step run your code using F8 and see where is it wrong, maybe the IF conditions are not coming to be true?
Step run your code while different sheets are active, maybe that is part of the problem.

 

[Ceslior]

Can you step run your code using F8 and see where is it wrong, maybe the IF conditions are not coming to be true?
Step run your code while different sheets are active, maybe that is part of the problem.

I apologise for the late response. I worked a bit on my code and it's working as intended now.
My only problem now is, I have 3 tables in my first sheet "Report" and one in "Report2". Do I have to create a bunch of IFs or am I able to put them together?

Private Sub Worksheet_Change(ByVal Target As Range)
    
'Save changing values
    
    If Not Intersect(Target, Range("C4:D4")) Is Nothing Then
    
    Range("C4:D4").Copy
    KW = Sheets("Report").Range("S8")
    
    Sheets("Total").Visible = True
    Sheets("Total").Select
    Sheets("Total").Range("1:1").Select
    
    Selection.Find(What:=KW, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(0, 1).PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
    Sheets("Total").Cells(1, 1).Select
    Sheets("Total").Visible = xlSheetVeryHidden
    Sheets("Report").Select
    
    End If
    
'Load all values

    If Not Intersect(Target, Range("S8")) Is Nothing Then
    KW = Sheets("Report").Range("S8")
    
    Sheets("Total").Visible = True
    Sheets("Total").Select
    Sheets("Total").Range("1:1").Select
    
    Selection.Find(What:=KW, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(, 1).Resize(1, 2).Copy
    
    Sheets("Report").Range("C4:D4").PasteSpecial Paste:=xlPasteValues
    Cells(1, 1).Select
    Application.CutCopyMode = False
    
    End If
    
End Sub