Sub ÏîäãîòîâêàÑâîäàÏîÎñòàòêàì()
' Ïîäãîòîâêà ñâîäà ïî îñòàòêàì ê äîáàâëåíèþ íîâîé íåäåëè
    Dim ReportingWeek As Integer
    Dim OldWeek As Integer
    Dim OldWeekRange As Range
    Dim ReportingWeekRange As Range
    Dim OldWeekRangeColumn As Range
    Dim ReportingWeekString As String
    Dim OldWeekString As String
    Dim DynamicRange As Range
    Dim SparkRange As Range
    Dim SparkSource As Range
    Dim SparkLocationRange As Range

Application.ScreenUpdating = False
'Ïîëó÷àåì íîìåð îò÷åòíîé, ïðåäûäóùåé íåäåëè, à òàêæå èõ òåêñòîâûå ýêâèâàëåíòû
    ReportingWeek = InputBox("Óêàæèòå íîìåð îò÷åòíîé íåäåëè")
    OldWeek = ReportingWeek - 1
    ReportingWeekString = (ReportingWeek) & " Íåäåëÿ"
    OldWeekString = OldWeek & " Íåäåëÿ"
    
'Ñêîïèðîâàòü îñíîâíîé ìàññèâ
    If Trim(OldWeekString) <> "" Then
        With Application.ActiveSheet.Range("3:3")
            Set OldWeekRange = .Find(What:=OldWeekString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
               

        End With
    End If
    OldWeekRange.Resize(, 6).EntireColumn.Copy
    Set OldWeekRangeColumn = OldWeekRange.Offset(, 6).EntireColumn
    OldWeekRangeColumn.Columns.Insert
    Set ReportingWeekRange = OldWeekRange.Offset(, 6)
    
'Óäàëÿåì äàííûå ïðîøëîé íåäåëè èç íîâîãî îñíîâíîãî ìàññèâà
    ReportingWeekRange.Offset(2, 0).Resize(86, 6).Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
   
'Îáíîâëÿåì ìàññèâ äèíàìèêè îò÷åòíîé íåäåëè
      If Trim(OldWeekString) <> "" Then
            With Application.ActiveSheet.Range("3:3")
                Set DynamicRange = .Find(What:="Äèíàìèêà îò÷åòíîé íåäåëè ê ïðåäûäóùåé", _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
               

            End With
        End If
    DynamicRange.Resize(, 6).EntireColumn.Copy
    
    DynamicRange.EntireColumn.Offset(, 6).Columns.Insert
    DynamicRange.Resize(, 6).EntireColumn.Delete
    
'Îáíîâëÿåì ìàññèâ äëÿ ñïàðêëàéíîâ
     If Trim(OldWeekString) <> "" Then
            With Application.ActiveSheet.Range("3:3")
                Set SparkRange = .Find(What:="Äèíàìèêà çà ïåðèîä", _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
               

            End With
        End If
    SparkRange.Offset(, -1).EntireColumn.Copy
    SparkRange.EntireColumn.Columns.Insert
    
'Îáíîâëÿåì ñïàðêëàéíû
    If Trim(OldWeekString) <> "" Then
            With Application.ActiveSheet.Range("3:3")
                Set SparkRange = .Find(What:="Äèíàìèêà çà ïåðèîä", _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
               

            End With
        End If
    Set SparkSource = SparkRange.Offset(3, -ReportingWeek).Resize(85, ReportingWeek)
    Set SparkLineLocationRange = SparkRange.Offset(3, 0).Resize(85, 1)
   SparkRange.Offset(3, 0).SparklineGroups.Item(1).Modify Location:=SparkLineLocationRange, _
        SourceData:=SparkSource.Address
Application.ScreenUpdating = True
 End Sub