New to VBA** Assistance - Database creation and monthly updates

[John_JingleHymerSmith]

Hi all, I am fairly new to learning VBA and have only dabbled in basic macro recording/understanding and minor editing of vba code via MOOCs. I would like to leverage these skills and learn more, as I am an analyst and sifting through massive databases is a daily tedious task.

I have created a master database in excel with over 200,000 rows of data. I would like to somehow construct a monthly macro that I can run to populate the current period's rows of data with a corresponding formula.

i.e. - Column A Range A1:A200601, if equal to P1, then populate x formula in column H1-Hxx. Would this be optimal or too time consuming/large?

This is instead of to having formulas populated through all 12 periods, which caused severe file crashes and I was just unable to work with it. Any help or advice is much welcomed please advise.

 

[Logit]

.
If I understand your requirements, this macro will accomplish your goal :

Option Explicit


Sub CopyYes()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    
    Dim myString As String


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
   
    myString = ActiveSheet.Range("P1").Value
    
    j = 2     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("A1:A200601")   ' Do 200,601 rows
        If c = myString Then
           c.Offset(0, 7).Formula = "=Now()"   'edit everthing between the quotation marks to the required formula
           j = j + 1
        End If
    Next c
    
End Sub

You can download a sample functional workbook from : https://www.amazon.com/clouddrive/share/ybVqjKUaZm2WBkyQgrb2OWbX2eLx6pN1KlEikNNVYLm