Hi Tanya

I assume you have two sheets in your workbook: Data & Template

The macro below will copy template sheet and rename the copy as "location",
then insert "location" in B2.

Now you can use your previous formula (insert the formula in Template sheet
before the macro is run): "=IF(Data!A2=B2,Data!M2)"

If you want the macro to populate the reports, further details is needed.
Can the row be copied to the target sheet if Location match, or ....

Sub CreateReport()
NoOfTabs = Sheets("Data").Range("A2").End(xlDown).Row - 1
Set TemplSh = Sheets("Template")
For sh = 1 To NoOfTabs
Set NewSh = TemplSh.Copy(After:=Sheets(Sheets.Count))
NewSh.Name = Sheets("Data").Range("A1").Offset(sh, 0).Value
Range("B1").Value = NewSh.Name
Next
End Sub

Hopes it helps

Regards,
Per

"Tanya" skrev i meddelelsen
...
I have a data file with 50 columns, including one named "location". There
are
hundreds of locations, with values including '01', '21', '71'. I also have
report tabs which contain a template - the number of report tabs matches
the
number of unique values in the location column, so in my example there are
three report tabs. The report tabs are identical to each other, except for
their name. I want to create a macro to help populate the report tabs (the
template contains formulas and calculations), based on the value of the
location column.

What I have been doing is manually inserting "=IF(Data!A2=71,Data!M2)" in
the report tab, to pull data from cell M2 of the master file, if the value
of
cell A2 = 71. What I want is to be able to do is to receive the master
file,
which will always have the variables in the same columns, and run a macro
which will dump the data into the pre-created report tabs. The number of
tabs
may differ from time to time, but that can be manually handled.

Ideas?