Looping copying and pasting columns from one sheet to another

Question

This should be a relatively easy thing to do, but please forgive me since I'm new with VBA. I'm trying to automate looping through copying columns from one sheet to pasting onto another. The columns are different lengths and the offsets are different between copying and pasting.

Here's what I have so far (it times out so not sure what's going on with my loop).

Sub LoopEveryTwoColumns() Dim original As Range Dim destination As Range Set original = Sheets("Sheet1").Columns("B") Set destination = Sheets("Sheet2").Columns("A") Dim x As Long For x = 1 To 3 original.Copy (destination) original.Offset(0, 2).Copy (destination.Offset(0, 1)) Next x End Sub

Here's what I want it to look like. The first picture is the original range on Sheet1. The second picture is the destination range that should be copied onto Sheet2.

Vityata · Accepted Answer · 2018-06-22 14:42:29Z

Without variables it is quite simple:

Sub TestMe() Dim x As Long For x = 1 To 3 Worksheets(1).Columns(x * 2).Copy Worksheets(2).Columns(x) Next x End Sub

The idea is that you need every second column - hence x * 2. And in the second worksheet, you need to paste it consequetively - thus x.

If you need to make it with variables (which is in general better), set the variables for the worksheet, not the columns:

Sub TestMe() Dim original As Worksheet Dim destination As Worksheet Set original = Worksheets(1) Set destination = Worksheets(2) Dim x As Long For x = 1 To 3 original.Columns(x * 2).Copy destination.Columns(x) Next x End Sub

BLitE.exe · Accepted Answer · 2018-06-22 14:43:06Z

Try this one instead.

For x = 1 To 3 original.Columns(x+1).Copy destination.Cells(1,x) Next x

Collectives™ on Stack Overflow

Looping copying and pasting columns from one sheet to another

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related