excel - macro to copy multiple cell ranges and paste in a row on another sheet -

-

i recorded macro, i'm trying obtain creating code copy following range in code on each worksheet , paste in rows underneath each other on sheet "master".

i have following code: 

sub macro1() ' ' macro1 macro '  '  dim rng range sheets("al-jackson hospital-fvar").select  set rng = range( _ "k50:m50,k58:m58,k59:m59,k55:m55,k12:m12,k14:m14,k24:l24,k28:l28,k29:l29,k35:l35,k62:l62,k32:l32,k30:l30,k31:l31,k63:l63,k33:l33,k34:l34,k37:l37,k40:l40,k41:l41,k42:l42,k46:l46" _     ) rng.select selection.copy sheets("master").select range("b4").select range("b4").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _     :=false, transpose:=false activewindow.scrollworkbooktabs position:=xlfirst  end sub

for example: on sheet 1, 2 ,3 copy following range on each sheet , paste values in sheet master starting in cell b1. sheet 1 data range should in b1, sheet 2 data range should in b2, , sheet 3 data range should in b3 , etc....

guys workbook has on 50 sheets

something should work you:

sub tgr()      dim wb workbook     dim ws worksheet     dim wsdest worksheet     dim rcell range     dim adata() variant     dim scells string     dim long, j long      set wb = activeworkbook     set wsdest = wb.sheets("master")     scells = "k50:m50,k58:m58,k59:m59,k55:m55,k12:m12,k14:m14,k24:l24,k28:l28,k29:l29,k35:l35,k62:l62,k32:l32,k30:l30,k31:l31,k63:l63,k33:l33,k34:l34,k37:l37,k40:l40,k41:l41,k42:l42,k46:l46"      redim adata(1 wb.sheets.count - 1, 1 wsdest.range(scells).cells.count)      = 0     each ws in wb.sheets         if ws.name <> wsdest.name             = + 1             j = 0             each rcell in ws.range(scells).cells                 j = j + 1                 adata(i, j) = rcell.value             next rcell         end if     next ws      wsdest.range("b1").resize(ubound(adata, 1), ubound(adata, 2)).value = adata  end sub

Comments

Post a Comment

Popular posts from this blog

html - Styling progress bar with inline style -

-
i trying set progress bar color red using inline style. need in way because have access <body> part of document. style need apply: progress { color: red } progress::-moz-progress-bar { background: red;} progress::-webkit-progress-value { background: red; } progress[aria-valuenow]:before { background: red; } i tried <progress style="background: red;"> incorrect effect, progress bar color turns blue red background. guide right direction desired style? thank in advance. here live example: https://jsfiddle.net/z36d3f6l/
Read more

java - Oracle Sql developer error: could not install some modules -

-
i have downloaded fresh copy of sqldeveloper ( sqldeveloper-4.1.3.20.78-no-jre ) oracle website. when unzip , lauch gui, keep getting following error message: warning - not install modules: dynamic module config - no module providing capability org.netbeans.netbinox found. my system: windows 7 32 bit hp probook. jdk 1.6 , 1.7, 1.8 available in java/jdk subdirectory. netbeans 8.0.2 installed finally got way out after dirty hours. extracted sqldeveloper zip in nested directory. i.e: e://a/b/c/sqldeveloper. starting sqldeveloper gui above directory threw erros. solution: go in c:\users**username**\appdata\roaming : delete sql developer , sqldeveloper folders extract sql developer zip in new non nested directory. i.e: e:// start , should work now reference: https://community.oracle.com/thread/3870680?start=0&tstart=0
Read more

How to use autoclose brackets in Jupyter notebook? -

-
this may sound silly question, how make use of autoclose brackets in jupyter notebook? example, when type print( jupyter notebook auto-closes brakets print() and places cursor inside. type argument, say print(1 + 1) now cursor between second 1 , right bracket ) . key navigate right of ) ? of course can press end or → or ) achieve this, not seem save time, purpose of brackets autoclose suppose? thanks in advance.
Read more