Copying Column to next one

Question

My Goal is to get a Macro that copies the whole Column R and pastes it into the next Column S. When i use the button again, the column R gets copied and it should be pasted into the column T.


Sub CopyPaste()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet


Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet1")

copySheet.Range("R:R").Copy
pasteSheet.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial


Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

But when I use the macro, the column gets copied, but it doesn't move to the next column and never gets pasted into the column R.

Is there anything in cell R1? If not, the right-most column will not update. — SJR, Aug 09 '23 at 08:54
I recommend reading [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424) - if you apply those adivses, the error will most likely be gone. — Ike, Aug 09 '23 at 08:54
`never gets pasted into the column R.` You don't want to paste it there, do you? — Black cat, Aug 09 '23 at 09:39
Data starts at R4. Everytime I use the Button it should be pasted into the next available blank column. — INeedHelpWithVbaPlease, Aug 09 '23 at 09:55

score 1 · Answer 1 · answered Aug 09 '23 at 10:04

To avoid empty cell issue use this

Sub CopyPaste()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet


Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet1")

copySheet.Range("R:R").Copy
pasteSheet.Columns(pasteSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column + 1).PasteSpecial


Application.CutCopyMode = False  
Application.ScreenUpdating = True
End Sub

VBasic2008 · Answer 2 · 2023-08-09T10:12:52.650

Copy Entire Column

Sub CopyEntireColumn()
    
    Application.ScreenUpdating = False
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim copySheet As Worksheet: Set copySheet = wb.Worksheets("Sheet1")
    Dim pasteSheet As Worksheet: Set pasteSheet = wb.Worksheets("Sheet1")
    
    Dim copyRange As Range: Set copyRange = copySheet.Columns("R")
    
    Dim pasteRange As Range:
    
    With pasteSheet.UsedRange
        Set pasteRange = .Resize(, 1).Offset(, .Columns.Count).EntireColumn
    End With
    
    copyRange.Copy pasteRange
    
    Application.ScreenUpdating = True

End Sub

Instead of introducing the range variables, you could use the shorter but less readable
(not recommended):

With pasteSheet.UsedRange
    copySheet.Columns("R").Copy .Resize(, 1).Offset(, .Columns.Count).EntireColumn
End With

Oran G. Utan · Answer 3 · 2023-08-09T10:08:08.547

No need to copy paste.

Sub copyRange()

Dim xLAppL As Excel.Application: Set xLAppL = GetObject(, "Excel.Application")
Dim wrkBk As Excel.Workbook: Set wrkBk = xLAppL.ActiveWorkbook
Dim wrksH As Worksheet: Set wrksH = wrkBk.ActiveSheet


Dim sourceArr() As Variant: sourceArr = wrksH.Range("r:r").Value2 '.CurrentRegion

    wrksH.Range("T1").Resize(UBound(sourceArr, 1), UBound(sourceArr, 2)).Value2 = sourceArr

End Sub

Copying Column to next one

3 Answers3

Copy Entire Column