VBA: Loop to copy/paste between worksheets

[Texas Longhorn]

Hi all,

I'm a VBA newb, and I have the following code, which does not work:

Sub SheetToSheet()

    For i = 1 To 367
        With Sheet2
            .Select
            .Range(Selection, Selection.End(xlDonw)).Copy
            .Cells.Offset(0, 1).Select
        End With
        With Sheet4
            .Paste
            Application.CutCopyMode = False
            .Cells.Offset(0, 2).Select
        End With
    Next i

End Sub

I'm trying to copy a range on Sheet2, move over one column, paste the copied range in Sheet4, move over two column, repeat. My main issue (I think/hope) is that I don't understand how to reference the active cell in each sheet (this code is a one-off, so I start with my active cell in Sheet2 as A16, and my active cell in Sheet4 as B16).

Thanks for any help,

Bill

 

[VoG]

In English, what do you want to copy from where to where. Please don't say ActiveCell, we need the cell reference.

 

[Texas Longhorn]

In Sheet2, I have 367 contiguous columns of data (A through NC). Each column is a different number of rows. In Sheet4, I have 734 columns (A through ABF) where every other column has data (so A, C, E, etc have data which is the same number of rows as Sheet2 columns A, B, C, etc, respectively). Columns B, D, F, etc are empty. I want to copy Sheet2 A16:ALastRow to Sheet4 B16, then Sheet2 B16:BLastRow to Sheet4 D16, etc.

Thanks for your help; please let me know if I can clarify further.

 

[VoG]

I think that you may have to loop through the source sheet, row by row, then copy and paste as appropriate.

 

[Texas Longhorn]

VoG,

Thanks for the reply. I might not be explaining this well. Were I to do this without VBA, heres what I would do: On Sheet2, starting in A16, I hold shift-end-down. Copy. Right arrow once to B16. Click Sheet4, click B16, Paste, right arrow twice to D16. Back to Sheet2, shift-end-down (from existing cell), copy, right arrow once, click Sheet4, Paste, right arrow twice, etc.

Hope this helps my explanation...thanks again for the help.

 

[Texas Longhorn]

Ok, I figured out some code that works (below). I'm still confused as to why my above code didn't do what this code does, so if anyone wants to enlighten me, that would be much appreciated.

Thanks.

Working code:

Sub SheetToSheet()

    For i = 1 To 367
        Sheet2.Select
        Range(Selection, Selection.End(xlDown)).Copy
        ActiveCell.Offset(0, 1).Range("A1").Select
        
        Sheet4.Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        ActiveCell.Offset(0, 2).Range("A1").Select
    Next i

End Sub

 

[jim may]

This worked for me.

Sub SheetToSheet()
Application.ScreenUpdating = False
    With Sheet2
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A16:A" & LR).Select
        .Range("A16:A" & LR).Copy
    End With
    With Sheet4
            .Activate
            .Range("B16").PasteSpecial
            Application.CutCopyMode = False
            Selection(1).Offset(0, 2).Select
        End With
    For i = 2 To 366
        With Sheet2
            .Activate
            Selection(1).Offset(0, 1).Select
           .Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
        End With
        With Sheet4
            .Activate
            Selection.PasteSpecial
            Application.CutCopyMode = False
            Selection(1).Offset(0, 2).Select
        End With
    Next i
Application.ScreenUpdating = True
End Sub

 

[Texas Longhorn]

Thanks for your help, Jim.

 

[jim may]

Did I understand your requirements and does the code perform as expected?