Macro to Copy Specific Columns from One Worksheet to Another in Excel (6 Methods)

Method 1 – Copy selected Columns from One Worksheet to Another Using Macro

The sample dataset contains some information about the employees of a company. We will use VBA code to copy Columns B & D here.

STEPS:

Go to the Developer tab and select Visual Basic.

The Visual Basic window will open.
Go to Insert and select Module.
Enter the below code in the Module window.

Sub Copy_Specific_Columns_ToAnother_Sheet()
Sheets("Copy Specific Columns").Range("B:B").Copy Sheets("Copied Columns").Range("A:A")
Sheets("Copy Specific Columns").Range("D:D").Copy Sheets("Copied Columns").Range("B:B")
End Sub

Here, we have declared the Sub procedure as ‘Copy_Specific_Columns_ToAnother_Sheet’. We selected the sheet from which we want to copy the columns inside Sheets(“”) and also selected the columns using the range. Then, we select the sheet where we wanted to copy the columns. We applied this code for Column B & Column D and copied them to Column A & Column B of another sheet.

Press Ctrl + S or click the Save icon to save the code.
Go to Macros in the Developer tab.

Select Run from the Macro window.

You can also run any code after saving it by pressing the F5 key.
The results are returned as below.

Method 2 – Copy Columns to Another Sheet with Formatting

STEPS:

Go to the Developer tab and select Visual Basic.

Go to Insert and select Module.
Enter the below code in the Module window.

Sub Copy_Specific_Columns_with_Format()
Sheets("Copy Specific Columns").Range("B:B").Copy Sheets("Copied Columns").Range("B:B")
Sheets("Copy Specific Columns").Range("D:D").Copy Sheets("Copied Columns").Range("D:D")
End Sub

This code copies Column B of the Copy Specific Columns sheet to Column B of Copied Columns sheet. If you want to copy more columns repeat the same command, changing the column in the range, which we have done for Column D here.

Press Ctrl + S or click the Save icon to save the code.
Go to Macros in the Developer tab.

Select Run from the Macro.

The results are returned as below.

Method 3 – Copy Columns from One Sheet to Another without Formatting

STEPS:

Go to the Developer tab and select Visual Basic.

Go to Insert and select Module.
Enter the VBA code in the Module window.

Sub Copy_Columns_WithoutFormat()
Range("B:B").Copy
Sheets("Copied Without Format").Range("A1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D:D").Copy
Sheets("Copied Without Format").Range("B1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Here, we have used the same method to copy the columns but we have used the Paste:=xlPasteValues to only paste values.

Press Ctrl + S or click the Save icon to save the code.
Select Macros from the Developer tab.

Click Run from the Macro window.

The results are returned as below.

Method 4 – Copy Column to a New Sheet with Formula

In this example, we have added the SUM function in our dataset to calculate the total salary. If the previous methods are used the Macro will only copy the values.

STEPS:

Go to the Developer tab and select Visual Basic. A Visual Basic window will appear.

Go to Insert and select Module.
Enter the code in the Module window.

Sub Copy_Column_with_Formula()
Range("F:F").Copy
Sheets("Copied With Formula").Range("B1").PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

The code uses Paste:=xlPasteFormulas instead of Paste:=xlPaste Values to paste with formula. We used only one column here, but to apply it to other columns, copy the code and paste it, changing the column in the range.

Press Ctrl + S to save the code.
Go to the Developer tab and select Macros.

Run the code from the Macro dialog box.

You will see the column containing the formula in another worksheet.

Method 5 – Copy Specific Columns to Another Sheet with AutoFit

STEPS:

Go to the Developer tab and select Visual Basic.
A Visual Basic window will appear.

Go to Insert and select Module.
Enter the code in the Module window.

Sub Copy_Columns_with_AutoFit()
Sheets("With AutoFit").Select
Range("B:B").Copy
Sheets("Copied With AutoFit").Select
Range("B1").Select
Sheets("With AutoFit").Select
Range("D:D").Copy
Sheets("Copied With AutoFit").Select
Range("C1").Select
ActiveSheet.Paste
Columns("B:F").AutoFit
End Sub

We have used the copy and paste method here and the AutoFit command at the end.

Click the Save icon or press Ctrl + S to save the code.
Select Macros from the Developer tab.

Select the desired code from the Macro window and click Run it.

The results are returned as below.

Method 6 – Copy Specific Columns Keeping Same Column Width

STEPS:

Go to the Developer tab and select Visual Basic to the Visual Basic window.

Go to Insert and select Module.
Enter the below code in the Module window.

Sub Copy_Columns_with_ColumnWidth()
Range("B:B").Copy Destination:=Sheets("Copied With ColumnWidth").Range("B1")
Range("B:B").Copy
Sheets("Copied With ColumnWidth").Range("B1").PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("F:F").Copy Destination:=Sheets("Copied With ColumnWidth").Range("F1")
Range("F:F").Copy
Sheets("Copied With ColumnWidth").Range("F1").PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub