How to loop through same column on specific sheets in excel vba? -
hi stackoverflow community:
i'd loop through same column on 3 specific sheets in workbook. understand there needs along lines of code similar what's been posted here , here, can't seem them work , instead receive error '1004', "application-defined or object-defined error".
additional notes: entered "for each ws in workbook.sheets" after declarations, , "next" @ end before "end sub". tried processing code after "r=1" in code below , received same 1004 error. tried "next" after loop code , still looped through first sheet.
this code:
sub makewordlist() dim inputsheet worksheet dim wordlistsheet worksheet dim puncchars variant, x variant dim long, r long dim txt string dim wordcnt long dim allwords range dim pc pivotcache dim pt pivottable application.screenupdating = false set inputsheet = activesheet set wordlistsheet = worksheets.add(after:=worksheets(sheets.count)) wordlistsheet.range("a1") = "all words" wordlistsheet.range("a1").font.bold = true inputsheet.activate wordcnt = 2 puncchars = array(".", ",", ";", ":", "'", "!", "#", _ "$", "%", "&", "(", ")", " - ", "_", "--", "+", _ "=", "~", "/", "\", "{", "}", "[", "]", """", "?", "*") r = 1 ' loop until blank cell encountered while cells(r, 7) <> "" ' covert uppercase txt = ucase(cells(r, 7)) ' remove punctuation = 0 ubound(puncchars) txt = replace(txt, puncchars(i), "") next ' remove excess spaces txt = worksheetfunction.trim(txt) ' extract words x = split(txt) = 0 ubound(x) wordlistsheet.cells(wordcnt, 1) = x(i) wordcnt = wordcnt + 1 next r = r + 1 loop ' create pivot table wordlistsheet.activate set allwords = range("a1").currentregion set pc = activeworkbook.pivotcaches.add _ (sourcetype:=xldatabase, _ sourcedata:=allwords) set pt = pc.createpivottable _ (tabledestination:=range("c1"), _ tablename:="pivottable1") pt .adddatafield .pivotfields("all words") .pivotfields("all words").orientation = xlrowfield end end sub
is there way can loop through first 3 sheets or identify sheets want loop through? specifically, i'd loop through column g of each of first 3 sheets.
i'd this, haven't had chance test out may need tidying up.
dim lsheets(2) lsheets(0) = "sheet1" lsheets(1) = "sheet2" lsheets(2) = "sheet3" application.screenupdating = false set wordlistsheet = worksheets.add(after:=worksheets(sheets.count)) wordlistsheet.range("a1") = "all words" wordlistsheet.range("a1").font.bold = true k = lbound(lsheets) ubound(lsheets) set inputsheet = sheets(lsheets(k)) inputsheet.activate 'rest of code' r = r + 1 loop next k ' create pivot table wordlistsheet.activate 'rest of code'
Comments
Post a Comment