excel - Loop through file names and merge select files -
so i'm new vba , have been hitting head against wall on 3 days now.
in sheet4, have list of file names in column c , "file type" in column h (ie. production, test, etc). combobox various file types.
i need able select file type combobox , have files file type merged onto different tab review.
below code have far it's complete mess , i'm beyond stuck.
sub mergefiles() dim rw long dim strsourcepath string dim strfile string dim filename string dim filetype string dim selectedtype string dim r long dim strdata string dim fi worksheet dim wkst worksheet dim lastrow string ' folder path files merged located strsourcepath = sheet1.textbox1.text if right(strsourcepath, 1) <> "\" strsourcepath = strsourcepath & "\" rw = 9 'row number first file name set fi = worksheets("fileinfo") filename = fi.cells(rw, "c").text filetype = fi.cells(rw, "h") selectedtype = sheet4.combobox1.text strfile = dir(strsourcepath & filename) application.screenupdating = false if len(filename) = 0 exit sub until strfile = "" if filetype <> selectedtype rw = rw + 1 filename = fi.cells(rw, "c").text filetype = fi.cells(rw, "h") elseif filetype = selectedtype workbooks.open filename:=strsourcepath & filename activesheet.range("a1", activecell.specialcells(xllastcell)).copy workbooks("quest psc data review take2").activate worksheets("fileinfo").activate lastrow = fi.cells(rows.count, "b").end(xlup).row + 1 range("a", lastrow).select selection.pastespecial rw = rw + 1 filename = fi.cells(rw, "c").text filetype = fi.cells(rw, "h") end if loop
Comments
Post a Comment