.Range("F13").Validation.Delete

 
Dim xWb As Workbook
Dim xNameString As String
Dim xName As Name
Set xWb = Application.ActiveWorkbook
xNameString = Application.InputBox("Name :", LIST_PROFILE, "", Type:=2)
Set xName = xWb.Names.Item(xNameString)
With xName
.RefersTo = .RefersToRange.Resize(52, 1)

Sub LoopThroughFiles()
Dim xFd As FileDialog
Dim xFdItem As Variant
Dim xFileName As String
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
xFileName = Dir(xFdItem & "*.xlsm*")
Do While xFileName <> ""
With Workbooks.Open(xFdItem & xFileName)
Dim xWb As Workbook
Dim xNameString As String
Dim xName As Name
Set xWb = Application.ActiveWorkbook
xNameString = Application.InputBox("Name :", LIST_PROFILE, "", Type:=2)
Set xName = xWb.Names.Item(xNameString)
With xName
.RefersTo = .RefersToRange.Resize(52, 1)
End With
xFileName = Dir
Loop
End With
End With
End Sub

Sub OpenAndCalc()
Dim myDir As String, fn As String, SaveFlag As Boolean
Application.ScreenUpdating = False
myDir = "C:\Users\username\Documents\"
fn = Dir(myDir & "*.xlsm*")
Do While fn <> ""
Workbooks.Open (myDir & fn)
Dim xWb As Workbook
Dim xNameString As String
Dim xName As Name
Set xWb = Application.ActiveWorkbook
xNameString = Application.InputBox("Name :", LIST_PROFILE, "", Type:=2)
Set xName = xWb.Names.Item(xNameString)
With xName
.RefersTo = .RefersToRange.Resize(52, 1)
End With
SaveFlag = Not ActiveWorkbook.ReadOnly
ActiveWorkbook.Close SaveFlag
fn = Dir()
Loop
End Sub

=ИНДЕКС(Sheet1!$A$2:$M$2,МАКС((""&A2=Sheet1!$A$3:$A$659)*(Sheet1!$B$3:$M$659>0)*СТОЛБЕЦ(Sheet1!$B$3:$M$659)))-1

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments:This macro takes in the different community costs in longview report
'           and paste it into the cost Finance tab
'
' Note: The dashboard must be saved as the same folder as longview data
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Sub FinanceImport(Optional control As IRibbonControl = Nothing)
 
    Dim i, j, k, n As Integer
    Dim lastrow As Integer
    Dim lrt As Integer
   
    Dim wbSource As Workbook
    Dim shtTarget As Worksheet
    Dim source As Worksheet
   
    Dim data As Variant
   
    Dim PasteRange As Range
    Dim Subcommunities As Range
    Dim SubcomsPasteRNG As Range
    Dim ComPasteRNG As Range
    Dim LongviewTab As Range
   
        Set shtTarget = ThisWorkbook.Worksheets("LongviewCommunity")
        ThisWorkbook.Activate
        Set wbSource = Application.Workbooks.Open(ThisWorkbook.Path & "\" & [rng_filename].Value)
       
        
        ThisWorkbook.Activate
        'shtTarget.Range("A7:B" & shtTarget.Cells(rows.Count, "D").End(xlUp).row).ClearContents
        'shtTarget.Range("D7:BJ" & shtTarget.Cells(rows.Count, "D").End(xlUp).row).ClearContents
       
        shtTarget.Range("A7:B140").ClearContents
        shtTarget.Range("D7:BJ140").ClearContents
       
        
            For Each LongviewTab In [LongviewTabs]
               If LongviewTab = "Transformation" Or LongviewTab = "Data & Analytics" Then
                    'do Nothing
                Else
                ReDim data(20, 60)
                Set source = wbSource.Worksheets(LongviewTab.Value)
               
                lastrow = source.Range("C82").End(xlDown).row - source.Range("C81").row
                Debug.Print lastrow
                lrt = shtTarget.Cells(rows.Count, "B").End(xlUp).row
                lrt = lrt + 3
                                                        
                            Set Subcommunities = source.Range(source.Cells(81, 2), source.Cells((81 + lastrow), 3))
                                For k = 0 To 4
                                    For i = 0 To lastrow
                                        n = 0
                                           
                                        For j = 1 To 15
                                            If j Mod 4 <> 0 Then
                                                   
                                            data(i, n) = source.Range(source.Cells(i + 82, j + 6), source.Cells(i + 82, j + 6)).Offset(0, 19 * k)
                                                   
                                                n = n + 1
                                                Else
                                            End If
                                        Next j
                                    Next i
                                       
 
                                Set PasteRange = shtTarget.Range(shtTarget.Cells(lrt, 4), shtTarget.Cells(lrt, 4)).Offset(0, 12 * k)
                                PasteRange.Resize(lastrow, UBound(data)).Value = data
                                
                                'Paste subcommunities
                                Set SubcomsPasteRNG = shtTarget.Range(shtTarget.Cells(lrt - 1, 1), shtTarget.Cells((lrt - 1 + lastrow), 2))
                                'Set SubcomsPasteRNG = shtTarget.Range(shtTarget.Cells(lrt, 2), shtTarget.Cells((lrt - 1 + lastrow), 2))
                                    SubcomsPasteRNG.Value = Subcommunities.Value
                                
                                'Paste community
                                Set ComPasteRNG = shtTarget.Range(shtTarget.Cells(lrt - 1, 1), shtTarget.Cells((lrt - 1 + lastrow), 1))
                                ComPasteRNG.Value = source.Cells(81, 2).Value
                        'do nothing
               
                Next k
                           
                        
                End If
                Next LongviewTab
        shtTarget.Range("BL7:BL150").Value = "Subcommunity"
        If Not wbSource Is Nothing Then wbSource.Close savechanges:=False
           
        log ("Processing complete")
        MsgBox "Longview report loaded", vbOKOnly + vbInformation, "Import processing"
 
   
End Sub