Copy and Paste Column Regardless of Worksheet Open

Question

I currently have 3 worksheets in my workbook ("SheetJS", "Sheet1", and "Sheet2").

I want the macro to copy and paste the values of Sheet1 from Column K to Column L. My code works but copies and pastes the columns on whichever worksheet is open/active.

I want the macro to code and paste the values only in "Sheet1" regardless of which one of the worksheets is open.

Any tips would help.

Thanks!

Sub Copy_K_to_L_2()
    With ThisWorkbook.Sheets("Sheet1")
          Columns("K:K").Copy Destination:=Columns("L:L")

    End With

End Sub

Columns("K:K").... is an Implicit reference to the ActiveSheet. You'll need to be Explicit about your sheet reference. In your case > put a . in front of Columns("K:K")... and Columns("L:L"). However, if you want to transfer just values, you don't need to use Copy/Paste at all but a value transfer would do. — JvdV

Adrian Celis Adrian Celis · Accepted Answer · 2020-01-07T08:18:51

Try to edit your existing line:

Columns("K:K").Copy Destination:= Thisworkbook.sheets("Sheet1").Columns("L:L")

Copy and Paste Column Regardless of Worksheet Open

1 Answers