excel - Pasting data from multiple sheet at desired place -


this code used copy data multiple sheets single sheet. want know if there way can copy data "report" sheet starting 3rd column, i.e, data should pasted sheet 3rd column onwards.

sub appenddataafterlastcolumn() dim sh worksheet dim destsh worksheet dim last variant dim copyrng range    application     .screenupdating = false     .enableevents = false end  ' delete summary worksheet if exists. application.displayalerts = false on error resume next activeworkbook.worksheets("report").delete on error goto 0 application.displayalerts = true  ' add worksheet name "report" set destsh = activeworkbook.worksheets.add destsh.name = "report"  ' loop through worksheets , copy data ' summary worksheet. each sh in activeworkbook.worksheets     if sh.name <> destsh.name   lastcol = destsh.cells(1, destsh.columns.count).end(xltoleft).column         ' find last column data on summary         ' worksheet.         last = lastcol   lastcol3 = sh.cells(1, sh.columns.count).end(xltoleft).column         ' fill in columns want copy.         set copyrng = sh.range(sh.cells(1, 2), sh.cells(15, lastcol3))          ' test see whether there enough rows in summary         ' worksheet copy data.         if last + copyrng.columns.count > destsh.columns.count             msgbox "there not enough columns in " & _                "the summary worksheet."             goto exitthesub         end if          ' statement copies values, formats, , column width.         copyrng.copy         destsh.cells(1, last + 1)             .pastespecial 8    ' column width             .pastespecial xlpastevalues             '.pastespecial xlpasteformats             application.cutcopymode = false         end      end if next  exitthesub:  application.goto destsh.cells(1)  application     .screenupdating = true     .enableevents = true end end sub

data sheet 1 comments:

enter image description here

data sheet 2 comments:

enter image description here

expected output comments:

enter image description here

this sort of copy can done copy. in order pick output range paste part, can use application.inputbox type:=8 parameter. prompts excel open range selection dialog works well.

once know 2 pieces, difficulty building ranges. not difficult, is specific context, existing data on sheets, , degree of robustness. example below, using currentregion block of data (same hitting ctrl+a) , intersect desired columns. can make use of usedrange , end build ranges.

picture of ranges shows different sheets input , final sheet output. sheet paste c empty now.

data , empty sheet

code work 2 ranges copy , prompts output location. there, pastes resulting ranges desired location. there offset ensure 2nd range not overlap first.

sub copyfromtworanges()      dim rng_set1 range     dim rng_set2 range      dim rng_output range      'build ranges     set rng_set1 = intersect(sheets("a").range("c:f"), _         sheets("a").range("c1").currentregion)      set rng_set2 = intersect(sheets("b").range("c:f"), _         sheets("b").range("c1").currentregion)      'prompt cell     set rng_output = application.inputbox("pick range", type:=8)      'ensure single cell     set rng_output = rng_output.cells(1, 1)      'paste ranges     rng_set1.copy rng_output     rng_set2.copy rng_output.offset(, rng_set1.columns.count)  end sub

result shows prompt cell selected , output.

enter image description here

enter image description here


Comments

Post a Comment

Popular posts from this blog

user interface - how to replace an ongoing process of image capture from another process call over the same ImageLabel in python's GUI TKinter -

i trying use following code create interactive gui changes color-space(hsv, rbg, grayscale etc) on event of button click. display opencv video in tkinter using multiprocessing being new python having problems multiprocessing , attempts on making gui change it's color space on button clicks hangs entire system. on it's implementation highly appreciated. thankyou. below code: import cv2 pil import image,imagetk import tkinter tk import numpy np multiprocessing import process , queue def quit_it(root,process): root.destroy() process.terminate() def black_andwhite(root,process): process.terminate p=process(target=capture_image, args=(5,queue, )) p.start() root.after(0, func=lambda: update_all(root, image_label, queue)) def update_image(image_label, queue): frame = queue.get() = image.fromarray(frame) b = imagetk.photoimage(image=a) image_label.configure(image=b) image_label._image_cache = b root.update() def update_all(root...
Read more

javascript - Using jquery append to add option values into a select element not working -

i'm looking dynamically populate select element names of items stored array. for reason it's not working , i'm not sure why. html: <div class="col-md-4 col-md-offset-4"> <select class="select1"> </select> , <select class="select2"> </select> </div> note i'm using bootstrap layout. here's jquery i'm using try , populate ".select1" jquery: select: function() { $.each(state.greens, function(index, value) { $(".select1").append("<option value =' " + index + " '>" + state.greens[index].name + "</option>"); }); } note function 'select' stored within object called 'display'. state.greens name of array i'm trying access. so @ end of html page call display.select(); call function. no error messages showing in console. also, saw question: appending ...
Read more

javascript - Restarting Supervisor and effect on FlaskSocketIO -

in index.html (html/javascript) have: $(document).ready(function(){ namespace = '/test'; var socket = io.connect('http://' + document.domain + ':' + location.port + namespace); socket.on('connect', function() { socket.emit('join', {room: 'venue_1'}); }); socket.on('my response', function(msg) { $('#log').append('<br>received #' + ': ' + msg.data); }); }); on server have: @socketio.on('connect', namespace='/test') def test_connect(): if session.get('venue_id'): emit('my response', {'data': 'connected'}) session.pop('venue_id', none) else: request.namespace.disconnect() @socketio.on('join', namespace='/test') def join(message): join_room(message['room']) room = message[...
Read more