Excel VBA to Work Across Workbooks -

i new vba coding , don't have understanding of doing honest. here go.

i looking see if:

• can vba codes have dyname values? instead of code saying execute on set sheet (e.g "sheet1") value changes depending value in cell.
• to trigger vba on workbook. example want run vba workbook a triggers vba on workbook b.

to explain want open workbook a (and workbook b if needed, doesn't matter) , click button runs vba on workbook b on sheet depending on value of cell in excel a (if cell says "sheet3" vba runs on "sheet3" on workbook b). want cells in workbook a reference cells in workbook b the sheet name dynamic. example have pasted basic cell reference bellow instead of having sheet1 want change depending on value in cell.

='[workbook b.xlsx]sheet1'!$a$4 

i know sounds complicates , confusing, if appreciated.

sub reportstepone()      dim myrow long       myrow = 4      rows(myrow).value = rows(myrow).value       dim rng range      set rng = range("a4:ac200")      rng.cut rng.offset(1, 0)      range("a1:ac1").copy range("a4:ac4")  end sub  

i want to:

1. edit code make fire on sheet
2. make sheet name referenced whatever in cell o sheet2 in report.xlsm.
3. run macro in report.xlsm runs above script (which called "stepone" in file called "historical data.xlsm"

the code below takes value of cell a4 on sheet2 in reports.xlsm , sets ws variable sheet in historical data.xlsm used rest of code. if possible i'd advise against having subs spread out on multiple projects opinion. think easier use proper referencing below.

since want button trigger on report.xlsm i'd suggest moving code workbook. if referenced can open, edit, save , close workbook single project again, in opinion easier calling subs in different project.

sub reportstepone()     dim wbhis workbook, wbrep workbook     dim strwsname string     dim ws worksheet      set wbhis = workbooks("historical data.xlsm")     set wbrep = workbooks("reports.xlsm")      strwsname = wbrep.worksheets("sheet2").cells(4, 1)     set ws = wbhis.worksheets(strwsname)      ws         .rows(4)             .value = .value         end         .range("a4:ac200")             .cut .offset(1, 0)         end         .range("a1:ac1").copy .range("a4:ac4")     end  end sub