How to export multiple selected worksheets to another workbook using VBA -
i have source excel file contains worksheets starting "typical" name. have code export "typical" worksheet excel file using getopenfile name. part of code, have rename source worksheet value contained in cell "e3" , current date.
attached code works fine me, can not select multiple "typical" sheets , export. can 1 suggest way loop through selected work sheets?
sub export() application.displayalerts = false application.screenupdating = false dim wb1 workbook dim wb2 workbook dim sht worksheet dim dt string dim mntg string set wb1 = activeworkbook set sht = selection.worksheet dim shtname string ' shtname = cstr(sht.name) dt = cstr(format(date, "ddmmyy")) if left(shtname, 7) = "typical" mntg = cstr(range("e2").value) sht.name = mntg & "_" & dt filetoopen = application.getopenfilename _ (title:="choose excel file insert selected typical file", _ filefilter:="*.xlsx (*.xlsx),") ' if filetoopen = false msgbox "no file specified.", vbexclamation, "error" sht.name = shtname application.displayalerts = true application.screenupdating = true exit sub else set wb2 = workbooks.open(filename:=filetoopen) end if wb1.activate sht.copy after:=wb2.sheets(wb2.sheets.count) wb2.save wb2.close else msgbox "this not typical file export", vbexclamation, "error" end if sht.name = shtname application.displayalerts = true application.screenupdating = true end sub
Comments
Post a Comment