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:

data sheet 2 comments:

expected output comments:

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.

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.