excel - After renaming the workbook vba is not working -

i'm still new vba, of coding recorded or modified after copying internet.

after recording steps performed, noticed macros record file name , sheet name.

so if open file not similar name in recorded macros, macros wouldn't work.

for example, file b name registered in macros wish perform macros program on file c failed.

is there way can bypass that?

sub trial()  dim wb, wb2, wb3 workbook  dim fn string  set wb = activeworkbook  application.filedialog(msofiledialogopen) .allowmultiselect = false if .show = -1 fn = .selecteditems(1) set wb2 = workbooks.open(fn) else msgbox "you cancel process." end if end  dim sheetname string  'this place edit  windows("spc july bpw341cl - copy.csv").activate     sheets("spc july bpw341cl - copy").select     sheets("spc july bpw341cl - copy").copy after:=workbooks( _         "new microsoft excel worksheet.xlsm").sheets(4)             sheets("spc july bpw341cl - copy").activate   sheetname = format(date, "yyyymmdd") 'change format per requirement activesheet.name = sheetname       set wb3 = application.activeworkbook      sheets("summary").activate     range("a1").select          each ws in wb3.worksheets             if ws.name <> "compare rgb" , ws.name <> "summary"                 = 1 5                     selection.value = ws.name                     selection.offset(0, 1).select             next         end if     next  end sub 

you're there.

at top, creating references workbooks can use later in code:

set wb2 = workbooks.open(fn) 

this creates reference workbook can use in code later.

when have code:

windows("spc july bpw341cl - copy.csv").activate sheets("spc july bpw341cl - copy").select sheets("spc july bpw341cl - copy").copy after:=workbooks( _     "new microsoft excel worksheet.xlsm").sheets(4)         sheets("spc july bpw341cl - copy").activate 

i presume recorded code? should able use references created earlier:

wb.sheets("spc july bpw341cl - copy").select wb.sheets("spc july bpw341cl - copy").copy after:=wb2.sheets(4) 

etc.

this may not want do, because i'm not entirely clear on sheets want copy workbook. have general syntax correct.

you can index sheets

dim osheet worksheet set osheet = wb.sheets("sheetname") '//to set via name, or set osheet = wb.sheets(4)            '//or set via index 

etc.

hope helps.

edit: couple of other points note: 1.) use option explicit @ top of code. 2.) dim wb, wb2, wb3 workbook - line doesn't declare 3 workbook objects. instead, declares 2 variants , workbook. need declare them as: dim wb workbook, wb2 workbook, wb3 workbook