excel - Deactivate entire sheet selection after paste -


i asked question , received great answer on site, running different problem. code below works running through each workbook in folder, copying sheet's contents, , pasting contents master workbook how like:

sub conslidateworkbooks() 'code pull sheets multiple excel files in 1 file directory 'into master "consolidation" sheet.  dim folderpath string dim filename string dim sheet worksheet dim wbname string  activesheet     range("a1").activate end  application.screenupdating = false folderpath = activeworkbook.path & "\" filename = dir(folderpath & "*.xls*") wbname = activeworkbook.name  while filename <> ""     if filename <> wbname         workbooks.open filename:=folderpath & filename, readonly:=true         each sheet in activeworkbook.sheets             copyorrefreshsheet thisworkbook, sheet         next sheet         workbooks(filename).saved = true         workbooks(filename).close         activesheet.range("a1").activate     end if     filename = dir() loop  application.screenupdating = true  end sub    sub copyorrefreshsheet(destwb workbook, sourcews worksheet)     dim ws worksheet     on error resume next     set ws = destwb.worksheets(sourcews.name)     on error goto 0     if ws nothing         sourcews.copy after:=destwb.worksheets(destwb.worksheets.count)     else         ws.unprotect password:="abc123"         ws.cells.clearcontents         sourcews.usedrange.copy         ws.range(sourcews.usedrange.address).pastespecial (xlpasteall)         application.cutcopymode = false         end if end sub

the problem having now: after paste completed, each sheet in master workbook has of cells selected, though ctrl+a'd entire sheet. rid of this. small task tried accomplish in line activesheet.range("a1").activate within do while .. loop, has not worked me.

edit:

i found solution works in case. not sure why necessary, because comments , answers in thread seem should work, did not. call sub before turn screenupdating true in main sub:

sub selecta1()     worksheets(1).activate     dim sheet worksheet      each sheet in activeworkbook.sheets         sheet.activate         activewindow.scrollrow = 1         activewindow.scrollcolumn = 1         sheet.range("a1").select     next sheet      worksheets(1).activate end sub

i realize more complicated should be, works purposes.

in copy sub, add in code in loop select cell should deactivate total used range selection , select coded range. 

sub copyorrefreshsheet(destwb workbook, sourcews worksheet) dim ws worksheet on error resume next set ws = destwb.worksheets(sourcews.name) on error goto 0 if ws nothing     sourcews.copy after:=destwb.worksheets(destwb.worksheets.count) else     ws.unprotect password:="abc123"     ws.cells.clearcontents     sourcews.usedrange.copy     ws.range(sourcews.usedrange.address).pastespecial (xlpasteall)     ws.range("a1").select     application.cutcopymode = false     end if end sub

i added ws.range("a1").select should described above.


Comments

Post a Comment

Popular posts from this blog

node.js - Node js - Trying to send POST request, but it is not loading javascript content -

i have been trying send post request website, , has not been loading full site. i guessing problem node js not loading javascript content, executing it. can see javascript in response, though never loads content javascript files producing in html. what mean is, doesn't execute javascript. how make node js code overcome this, , wait the full website load? have tried native code, , "request' module, both producing same errors. if helps, don't need native javascript. modules fine. some code have tried (request module): var options = { method: 'post', uri: 'https://url', formdata: { 'email': 'email', 'prize': '0', 'transactional': 'on' }, headers: { /* 'content-type': 'application/x-www-form-urlencoded' */ // set automatically } }; rp(options) .then(function (body) { console.log(body) }) .catch(function (err) { console.log(err) }); when fetch content...
Read more

javascript - Replicate keyboard event with html button -

i have tried few ways make happen. have javascript pinball game uses keyboard keys controls. converting application touch screen display. workaround, hoping overlay buttons when clicked simulate keyboard strokes. have done research , sounds should work. using jquery v3.2.1 here things have tried far... html element: <input type="button" class="coil"></input> $('.coil').trigger({type: 'keydown', which: 40, keycode: 40}); with this, not errors, nothing happens. var coilkey = $('body').keydown(function(e) { switch (e.keycode) { case 40: break;} }) $('.coil').click(coilkey); this 1 returns error in console... ((jquery.event.special[handleobj.origtype] || (intermediate value)).handle || handleobj.handler).apply not function below javascript game engine handles key listeners... addkeylistener: function (keyandlistener) { this.keylisteners.push(keyandlistener); }, // gi...
Read more

javascript - Web audio api 5.1 surround example not working in firefox -

i've been playing lott lately web audio api on both firefox , chrome. few days ago started experiment surround set. when trying surround audio in web audio api came notice example works fine in chrome v59 not in firefox v54. // create web audio api context var audioctx = new (window.audiocontext || window.webkitaudiocontext)(); audioctx.destination.channelinterpretation = 'discrete'; audioctx.destination.channelcountmode = 'explicit'; audioctx.destination.channelcount = 6; var oscillators = []; var merger = audioctx.createchannelmerger(6); console.log(audioctx.destination, merger); //merger.channelinterpretation = 'discrete'; //merger.channelcountmode = 'explicit'; //merger.channelcount = 6; var addoscilator = function(channel, frequency) { var oscillator = audioctx.createoscillator(); oscillator.frequency.value = frequency; // value in hertz oscillator.connect(merger,0,channel); oscillator.start(); oscillators.push(o...
Read more