Create template based off criteria

[mkting123]

Hi All,

I have received some help on this, but still need some guidance. I am trying to make an automated template for others to fill out based on some criteria (i.e., the brand its for and the report they're working on). The template should populate with row headers based on a drop down list in a different tab. Understand that this can be done by Vlookup, but for other purposes it needs to be a macros.

The first tab is the Criteria, which in A1 reads Brand, and then A2 reads Report. There are a list of brands under the Brand header that can be chosen, and then a list of reports that can be chosen. I want a macros to read these selections, then search for them in the second tab called Template Options. Once the brand and report are identified in the Template Options tab (which lists all of the metrics I want to be pulled for the template next to the brand and report names in the adjacent columns), it will populate in the Output tab all the metrics that are needed for that report and brand.

Right now the macros is just populating the criteria in the output page. I need the metrics that are required for that report, however, to populate based on that criteria only.

Right now, I have this beginning:

Private Sub Worksheet_Change(ByVal Target As Range)
    

    Dim brand, report, partner As String
    
    brand = Sheets("Criteria").Cells(2, 1)
    report = Sheets("Criteria").Cells(2, 2)

    With Sheets("Output")
        .Cells(4, 1) = brand
        .Cells(4, 2) = report
        .Cells(4, 3) = partner
    End With
    
    Call reportCheck(report)
    
End Sub


Any thoughts? Tried searching other questions, but had difficulty with them.

 

[iliace]

Your code looks well formed. However:

You said

The first tab is the Criteria, which in A1 reads Brand

but then you have this:

[COLOR=#333333]brand = Sheets("Criteria").Cells(2, 1)[/COLOR]

.Cells references (row, column). So you have row 2, column 1. That's A2, not A1. Could this be part of your error,

Similarly, you said

and then A2 reads Report

but your code is like this:

r[COLOR=#333333]eport = Sheets("Criteria").Cells(2, 2)[/COLOR]

That is B2, not A2.