Macro to Copy Specific Columns from One Worksheet to Another in Excel

In this article, we will learn some macro codes to copy specific columns from one worksheet to another worksheet in Excel. There are different ways to copy columns from one worksheet to another. You can use the manual ways also. Again, there are different types of copying and pasting. Keeping all these things in mind, we will discuss some effective macros to copy columns to another worksheet.

How to Copy Specific Columns from One Worksheet to Another Worksheet: 6 Excel Macro Examples

1. Copy Certain Columns from One Worksheet to Another Worksheet Using Macro

There are some times, we need to copy certain columns from our worksheet to another worksheet. To explain this example, we will use a dataset that contains some information about the employees of a company. We will use VBA code to copy Columns B & D here.

Let’s follow the steps to know more.

STEPS:

• In the first place, go to the Developer tab and select Visual Basic.

• The Visual Basic window will open.
• Now, go to Insert and select Module.
• Then, type the 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 first 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.
• Next, go to Macros in the Developer tab.

• After that, select Run from the Macro window.

• You can also run any code by pressing the F5 key after saving it.
• Finally, you will see results like below.

Read More: How to Use Excel VBA to Copy Range to Another Excel Sheet

2. Macro to Copy Particular Columns to Another Sheet with Format

We can also copy particular columns to a new sheet with the format. VBA makes it very easy.

Let’s pay attention to the steps below.

STEPS:

• First, go to the Developer tab and select Visual Basic. It will open the Microsoft Visual Basic for Applications window.

• Second, go to Insert and select Module.
• Third, type the 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 the columns in the same position as the format. It copies Column B of the Copy Specific Columns sheet to Column B of Copied Columns sheet. If you want to copy more columns, then just repeat the same command changing the column in the range. We have done this for Column D here.

• Press Ctrl + S or click the Save icon to save the code.
• Then, go to Macros in the Developer tab.

• Next, select Run from the Macro Make sure you select the desired code.

• Alternatively, we can also run any code by pressing the F5 key after saving it.
• Lastly, you will see results like below.

Read More: Excel VBA to Copy Rows to Another Worksheet Based on Criteria

3. Copy Definite Columns from One Sheet to Another without Format

Copying definite columns from one sheet to another without format is also easy. Here, we will just copy the values here.

Let’s follow the steps to learn this example.

STEPS:

• In the beginning, go to the Developer tab and select Visual Basic.

• The Microsoft Visual Basic window will occur.
• Now, go to Insert and select Module.
• Type 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 but we have used the Paste:=xlPasteValues to paste only values.

• Then, press Ctrl + S or click the Save icon to save the code.
• After that, select Macros from the Developer tab.

• Click Run from the Macro window.

• After running the code, you will see the result in the desired sheet.

Read More: Excel VBA: Copy Range to Another Workbook

4. Excel VBA Copy Column to a New Sheet with Formula

Sometimes we have formulas in our worksheet. But if we follow the previous example, it will only copy the values. To explain this example, we have added the SUM function in our dataset to calculate the total salary.

Observe the steps below carefully.

STEPS:

• Firstly, go to the Developer tab and select Visual Basic. A Visual Basic window will appear.

• Secondly, go to Insert and select Module.
• Then, type 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

Here, we have used Paste:=xlPasteFormulas instead of Paste:=xlPaste Values to paste with formula. We used only one column here. But you can use it for several other columns. To do so, just copy the code and paste it, changing the column in the range.

• Now, press Ctrl + S to save the code.
• After that, go to the Developer tab and select Macros.

• Lastly, Run the code from the Macro pop-up.

• In the end, you will see the column containing the formula in another worksheet.

• You can use this example for multiple columns also.

5. Macro to Copy Specific Columns to Another Sheet with AutoFit

In this example, we will copy specific columns from one worksheet to another with AutoFit. We will use the same dataset here.

Let’s follow the steps for more.

STEPS:

• To start, go to the Developer tab and select Visual Basic.
• A Visual Basic window will appear.

• Next, go to Insert and select Module.
• Type the VBA Code there.

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 AutoFit command at the end.

• Now, click the Save icon or press Ctrl + It will save the code.
• After that, select Macros from the Developer tab.

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

• Finally, you will see results like below in the expected sheet.

Read More: Macro to Copy and Paste from One Worksheet to Another

6. Copy Specific Columns Keeping Same Column Width

Sometimes, we need to keep the column width unchanged. We can copy the columns keeping the column width the same using simple VBA. Again, we will use the same dataset here.

Pay attention to the steps below.

STEPS:

• Firstly, you need to go to the Developer tab and select Visual Basic. It will open the Visual Basic window.

• Secondly, go to Insert and select Module.
• Now, type the 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

Here, we have copied Column B and Column F. We have used Paste:=xlPasteColumnWidths to keep the column width the same.

• Next, go to Macros.

• Select the code from the Macro window and Run it.

• We can also run any code by pressing the F5 key after saving it.
• Finally, you will see the desired results in another worksheet.

Download Practice Book

Download the practice book here.

Conclusion

We have demonstrated 6 examples of how to copy columns from one worksheet to another using macros here. I hope these examples will help you to solve problems related to copying columns. Furthermore, we have also added the practice book at the beginning of the article. You can download and exercise it to become an expert. Last of all, if you have any queries or suggestions, feel free to ask in the comment section.

Related Articles

• How to Open Another Workbook and Copy Data with Excel VBA
• Excel VBA to Copy Data from Another Workbook without Opening
• Macro to Copy Data from One Workbook to Another Based on Criteria
• Excel VBA to Loop Through Files in Folder and Copy Data
• How to Open All Excel Files in a Folder and Copy Data Using VBA