Copy multiple columns to end of range without using .Copy or .Cut

rossross

New Member
Joined
Apr 11, 2022
Messages
39
Office Version
  1. 365
Platform
  1. Windows
I'm trying to get better at this and in doing so, I've seen over and over again to stop using .Select and .Copy/.Cut to move things

I know I can move my columns with copy and insert no issue, but I'd like to step into the next level. On one ws, i've got columns P to AB and need to move them to another ws and insert these at the end of my data. my code as is, is just placing all the columns over each other. Meaning Column P is placed at the end and then Column Q is placed right over it in the same column and so on. so P to Q should now be AO to BA but it's all overlapping at AO and the last column is the one showing.

VBA Code:
dim i as long 
dim j as long
dim max as long
dim endcol as long 
dim targetcol as long 
dim lrow as long 

set sel = thisworkbook.worksheets... 
set ws = thisworkbook.worksheets.... 

max = 33 

with ws    

endcol = .cells(7, .columns.count).end(xltoleft).column    
endcol = endcol + 1 end with with sel   
lrow = .cells(rows.count, 1).end(xlup).row    

for i = 16 to max    
    targetcol = endcol       
        for j = 1 to lrow           
         ws.cells(j,targetcol).offset(6) = .cells(j,i) 'offset moves it down to start at row 7 where i need it      
         next j    
next i 

end with
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top