Snasui Excel Forum

Code: Select all

Sub Update_month()
'
'
'''' May23  (sheet GGG_0523)
    
    Range("B112").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range("D120").Select
    Sheets("GGG_0523").Select
    Range("B15").Select
    ActiveSheet.Paste
    Range("Q17").Select
    
'''' April23  (sheet GGG_0423)
       Range("B7:R7").Select
    Selection.Copy
    Sheets("GGG_0423").Select
    Range("B6").Select
    ActiveSheet.Paste
    Range("R2").Select
    Sheets("GGG_2023").Select
    Range("Q3").Select
    
'''' Mar23  (sheet GGG_0323)
    Range("B8:F10").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("B8:R10").Select
    Selection.Copy
    Sheets("GGG_0323").Select
    Range("B6").Select
    ActiveSheet.Paste
    Range("G13").Select
    Sheets("GGG_2023").Select
    Range("S3").Select
    Application.CutCopyMode = False
    Range("H3").Select
    
    
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rall As Range, r As Range
    Dim strSh As String
    If Target.Address = Me.Range("f1").Address Then
        With Worksheets("GGG_2023")
            Set rall = .Range("g7", .Range("g" & .Rows.Count).End(xlUp))
            For Each r In rall
                strSh = "GGG" & VBA.Format(r.Value, "_mmyy")
                If r.Value = Target.Value Then
                    With Worksheets(strSh)
                        .Range("b" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 22).Value = _
                            r.Offset(0, -5).Resize(1, 22).Value
                    End With
                End If
            Next r
        End With
    End If
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rall As Range, r As Range
    Dim strSh As String
    If Target.Address = Me.Range("f1").Address Then
        With Worksheets("GGG_2023")
            Set rall = .Range("g7", .Range("g" & .Rows.Count).End(xlUp))
            For Each r In rall
                strSh = "GGG" & VBA.Format(r.Value, "_mmyy")
                If r.Value = Target.Value Then
                    With Worksheets(strSh)
                        .Range("b" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 22).Value = _
                            r.Offset(0, -5).Resize(1, 22).Value
                    End With
                End If
            Next r
        End With
    End If
    
result = Application.WorksheetFunction.CountIf(Range("G7:G20"), "")
 
    MsgBox " Finished "
End Sub

Code: Select all

'Other code
With Worksheets("GGG_2023")
    Set rall = .Range("g7", .Range("g" & .Rows.Count).End(xlUp))
    If Application.WorksheetFunction.CountIf(rall, Target.Value) = 0 Then
        MsgBox "Not found", vbInformation
        Exit Sub
    End If
    For Each r In rall
'Other code