Combine Workbooks and Rename imported worksheet using VBA in Excel -

i trying import xls files in particular directory 1 workbook. i've tried several sources code , closest i've come 1 below (all rest complained when closing imported workbook no matter tried).

all want take text merged cell (c7 , d7) , rename new worksheet that. (there carriage return above name in cell in case has impact. have no control on source files they're produced external team).

i'm afraid have no ability coding of kind can futz things reading code other sources stumped here. i've managed rename source filename prefer cell text.

cheers!

sub merge2multisheets() dim wbdst workbook dim wbsrc workbook dim wssrc worksheet dim mypath string dim strfilename string      application.displayalerts = false     application.enableevents = false     application.screenupdating = false     mypath = "c:\temp\" ' change suit     set wbdst = workbooks.add(xlwbatworksheet)     strfilename = dir(mypath & "\*.xls", vbnormal)      if len(strfilename) = 0 exit sub      until strfilename = ""              set wbsrc = workbooks.open(filename:=mypath & "\" & strfilename)              set wssrc = wbsrc.worksheets(1)              wssrc.copy after:=wbdst.worksheets(wbdst.worksheets.count)              activesheet.name = wssrc.range("c7").value              wbsrc.close false          strfilename = dir()      loop     wbdst.worksheets(1).delete      application.displayalerts = true     application.enableevents = true     application.screenupdating = true  end sub 

• i change line:
  activesheet.name = wssrc.range("c7").value
  to:
  wbdst.worksheets(wbdst.worksheets.count).name = wssrc.range("c7")
  ensure you're naming sheet in destination workbook, not in source workbook, might end using activesheet

• from question in comment order of files:
  (btw - should edit post , put question in there, comments can deleted)
  order determined "natural" sort order files held in os. have not found flags can added dir() command sort them on input.
  if need process them in name order, suggest:

  1. create scratch sheet in wbdst
  2. loop through files using dir(), putting them in range(a1:an)
     • i.e put first file name in range("a1"), second file name in range("a2"), etc
  3. sort range(a1:an) in desired order
  4. loop through sorted range() actual processing
  5. delete scratch sheet wbdst when you're done processing

• for now, comment out:
  application.displayalerts = false
application.enableevents = false
application.screenupdating = false
  these great things have in code, not until working properly

• i suggest changing:
  if len(strfilename) = 0 exit sub
  to
  if len(strfilename) > 0 then
do until...
loop
  because, if initial read of directory gives no files, never clean code after loop. @ moment, there's nothing critical there, may modify code in future, or use model other code require critical clean up, , it's habit in.