-- This is a very simplified concept
create or replace
    function get_val (p_table_name varchar2)
           return varchar2 SQL_Macro 
    is
      return 'select col1,col2 from p_table_name'; 
    end;
/

select col1, col2
  from get_val(t.table_name)
     , table_list t;  --Table_list contains the list of the table to take

You need to pass tables to macros using the dbms_tf.table_t parameter. When calling the macro, you pass the table name itself, not a string containing the table name.

For example, this fails because the table_name parameter is a string:

create or replace function mcro ( table_name varchar2 ) 
  return clob sql_macro
as
begin 
  return 'select count(*) from table_name';
end;
/

select * from mcro ( 'employees' );
ORA-00942: table or view "CHRIS"."TABLE_NAME" does not exist

Instead of replacing the table_name with the parameter valud, the query searches for a table called "TABLE_NAME" (as the error shows).

Here's the revised version which enables you to pass the table:

create or replace function mcro ( table_name dbms_tf.table_t ) 
  return clob sql_macro
as
begin 
  return 'select count(*) from table_name';
end;
/

select * from mcro ( employees );

So you can't use rows in another table to pass the table names to the macro.

If you want to do this, you could either rewrite the function to use dynamic SQL

Or generate a script of the macro calls with a query like:

select  
 ' select * from mcro ( "' || table_name || '" ); '
from user_tables;