Copying and autoupdating information from a master sheet to secondary sheets

[05Li139]

Hi Everyone,

I've included a cleaned version of the workbook I will be using (the native file includes commercially sensitive information) that is identical in layout and structure to the native workbook.

I'm trying to set up a workbook that copies information from ['Raw_Data' columns B, D, E, H, I, J, P, R, S] to a new worksheet generated based on the 'Template' tab of the same workbook.

I've created a form inside 'Worksheet_Update_Tab' that pulls up information from ['Reference_Categories' columns B, C, D], with the idea that at the end of the form there will be a button that pulls all of the rows from the 'Raw_Data' Tab that have a cell matching the concatenated value in column D. It will then assign those to a reporting week as identified in the form, and paste the newly updated values for each week into a sheet generated named after the value in ['Worksheet_Update_Tab', D4]. If a worksheet already exists with this title, the information for the new reporting week should be pasted below the values generated the week earlier.

This is to allow dashboards to be built and trend analysis to take place.

I've previously used this code in another workbook to copy data and associated values from one form into another new/existing worksheet, however the ranges that this was looking at were limited to one worksheet; can this be modified to achieve what I'd like to?


Thank you for the help.

---

[
Sub UpdateLogWorksheet()
    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim nextRow As Long
    Dim oCol As Long
    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    
    myCopy = ("F3:F7, J12:J25")
    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("OutputAll")
    With historyWks
        nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
    End With
    With inputWks
        Set myRng = .Range(myCopy)
    End With
    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
      
      With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1)
         End With
      On Error GoTo 0
    End With
End Sub