I am looping over multiple sheets in an Excel with some macros. Within the loop, I need to add a prefix to a subset of variables (the subset changes in each sheet). I define a "varlist" to exclude variables that don't need the prefix (these variables are the same across sheets: "A B" in the codes below).
Code:
* import the excel file to determine number of sheet
import excel using "CE.xlsx", describe
forvalues sheet=1/`=r(N_worksheet)' {
    local sheetname=r(worksheet_`sheet')
    import excel using "CE.xlsx", sheet("`sheetname'") 
    ds A B, not
    foreach v of var `r(varlist)' {
    rename `v' ce`sheet'_`v'
    }
    save "ce_`sheetname'", replace
    clear
}
But it appears that Stata fails to recognize the "r(worksheet_#)" in the loop. I'd appreciate any help with this problem. Thank you.