Consulting

Results 1 to 2 of 2

Thread: Copying sheets and updating values from a main sheet

  1. #1

    Copying sheets and updating values from a main sheet

    Hi guys,

    I have a sheet 'matrix' which has a list of names across the row 2.
    They each then have a list of values.
    I'm trying to make a macro that will create a new sheet for each of the names based on a template and then add the values with the date it was run at the top. Then when run again it should add another column with the date at the top and only show values that have been changed.

    So far creating the sheets works fine. Adding the values was working but seems to have broken and now I'm stuck. Sometimes it seems to work correctly, other times there will be values and no dates, sometimes all of the values rather than just those that have changed.

    Please can anyone offer any advice?
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    '   
    Dim nextCol As Long, firstRow As Long, lastRow As Long, lastVal As Long
    Dim n As Variant, nm As Variant, nms As Variant
    
    Const m = "Matrix"
    
    Dim d As String
    
        d = Date
    
         Set nms = Sheets(m).Range("E2", Sheets(m).Range("E2").End(xlToRight)) ' Set range as names
    
        For Each nm In nms
            If DoesSheetExists(nm.Value) Then
            Else
               Sheets("Template").Copy After:=Sheets(Sheets.Count) 'Copies the template worksheet
               Sheets(Sheets.Count).Name = nm.Value ' renames the new worksheet
           End If
       Next nm
             
        with Sheets(m)
            nms = Application.WorksheetFunction.Transpose(nms)
       End With
    
       For Each nm In nms ' loop through names
           n = Application.WorksheetFunction.Match(nm, Sheets(m).Range("2:2"), 0) ' find the column this name appears in on the matrix
        
           'enter changes on the individual sheet
           With Sheets(nm)
              nextCol = .Cells(2, 1) ' get column number for entries
              lastRow = .Range("C" & .Rows.Count).End(xlUp).Row 'last row of tasks
                    
              For firstRow = 3 To lastRow
                 lastVal = Cells(firstRow, Columns.Count).End(xlToLeft).Column ' locate last column with an entry
                 If .Cells(firstRow, lastVal) = Sheets(m).Cells(firstRow, n) Then  ' if the last value matches the value in the matrix
                 Else: .Cells(firstRow, nextCol) = Sheets(m).Cells(firstRow, n) ' add the value ot the next empty column
                 End If
             Next firstRow
                        
             If Application.WorksheetFunction.Sum(Columns(nextCol)) = 0 Then
             Else
                .Cells(2, nextCol) = d
             End If 
         End With
      Next nm
    End Sub
    
    
    Function DoesSheetExists(sh As String) As Boolean
        Dim ws As Worksheet
    
        On Error Resume Next
           Set ws = ThisWorkbook.Sheets(sh)
        On Error GoTo 0
    
        If Not ws Is Nothing Then DoesSheetExists = True
    End Function
    Last edited by SamT; 06-10-2021 at 12:36 PM. Reason: Removed white space and unused code lines

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    I'm sorry, I just can't keep my brain straight reading the code when I must keep going back to determine what d,m,n,nm,nms,and xyz refer to.

    There is a reason why Best Practices insists on using meaningful variable names. It's for simple minds like mine.

    Not to worry, Paul will probably respond next, and he is sharp.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •