CODEWITHSUNDEEP
vbaexcel
user3314445
user3314445

Reputation: 75

Looping copying and pasting columns from one sheet to another

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.

Original Range on Sheet1

Destination Range on Sheet2

Upvotes: 1

Views: 482

Answers (2)

BLitE.exe
BLitE.exe

Reputation: 321

Try this one instead. 

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

Upvotes: 0

Vityata
Vityata

Reputation: 43575

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

Upvotes: 1

Related Questions