Excel VBA - update only the differences in cells in excel site 2

[jalmbris]

Hi everyone,

I'm trying to update only the differences in cells in excel site 2, site 3 ect that I compare with in the information in dash sheet.

I'm also not entirely sure if I've managed to describe what I want to achieve, right now I'm tired because I'm stuck and can't move forward and lacks some skills in excel vba.

a little background to this is that I will have a little over 100 excel sheets that will be
different sites and in each site there are different devices which of course have different forms of passwords.

What I have done is created a "dash" with buttons for each site where I also retrieve information from (sheet 2) "site 1" to (sheet 2) "dash"
to avoid scrolling through the tabs because there will be so many, and in "dash" (sheet 1) I want to be able to update the information for each site,
and then update the specific site's tab. one at a time I should add, and that means not all at once.

in sheet 1 "dash" I have information in L28 to L34 because since the information is presented there and you will
also editing the information in these, but the code seems to only want to fetch from column 1 only.
now I've been trying for a long time with this so I can remember a little wrong at the moment of writing.

I have also disabled buttoncolors ( 'Call siteButtons.ChangePassColor ) because in this document I get error in that code but not in another document,
but feel free to activate it because it should color the shape that is active, which makes it easier to understand where you are in the document and site.

updating password is handled by ( Call updateDiff.updateDiff ), but only wants to retrieve information from column A and also it writes date for
the lines that have not been changed. it is this function that, among other things, I cannot get in order no matter how I have tried. it doesn't need to retrieve headers for each "info"
because it is the same for everyone, so only the essential information is enough, but it needs to update the information that I get from the Site sheets to the right place.

the goal of the whole document is to be able to reuse existing code as much as possible since there will be over 100 sites which means that it would
be difficult to manage for each site. I guess you need to put all or as much of the code in Module instead of worksheet.

Sub updateDiff()
    Dim array1() As Variant
    Dim array2() As Variant
    Dim i As Long, j As Long
    Dim numRows As Long, numCols As Long
    Dim sitePw As Variant
    Dim dash As Variant
    
    Set sitePw = ThisWorkbook.Sheets("Site 1")
    Set dash = ThisWorkbook.Sheets("Dash")
    
    'assign arrays
    array1 = dash.Range("L28:L34").Value
    array2 = sitePw.Range("B3:B9").Value
    
    
    'get array dimensions
    numRows = UBound(array1, 1)
    numCols = UBound(array1, 2)
    
    'compare arrays
    For i = 2 To numRows
        For j = 1 To numCols
            If array1(i, j) <> array2(i, j) Then
                sitePw.Range("F" & i + 1).Value = sitePw.Range("B" & i + 1).Value
                sitePw.Range("B" & i + 1).Value = dash.Range("a" & i + 1).Value
                sitePw.Range("D" & i + 1).Value = Date
                
            End If
        Next j
    Next i
    
    
End Sub

 

[jalmbris]

Images of the sheets.