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 copy and pasting. Keeping all these things in mind, we will discuss some effective macros to copy columns to another worksheet.


Download Practice Book

Download the practice book here.


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

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.

Copy Certain Columns from One Worksheet to Another Worksheet Using Macro

Let’s follow the steps to know more.

STEPS:

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

Copy Certain Columns from One Worksheet to Another Worksheet Using Macro

  • 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

Copy Certain Columns from One Worksheet to Another Worksheet Using Macro

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.

Copy Certain Columns from One Worksheet to Another Worksheet Using Macro

  • After that, select Run from the Macro window.

Copy Certain Columns from One Worksheet to Another Worksheet Using Macro

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

Copy Certain Columns from One Worksheet to Another Worksheet Using Macro

Read More: How to Update One Excel Worksheet from Another Sheet Automatically


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.

Macro to Copy Particular Columns to Another Sheet with Format

  • 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

Macro to Copy Particular Columns to Another Sheet with Format

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.

Macro to Copy Particular Columns to Another Sheet with Format

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

Macro to Copy Particular Columns to Another Sheet with Format

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

Macro to Copy Particular Columns to Another Sheet with Format

Read More: How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)


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.

Copy Definite Columns from One Sheet to Another without Format

  • 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

Copy Definite Columns from One Sheet to Another without Format

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.

Copy Definite Columns from One Sheet to Another without Format

  • Click Run from the Macro window.

Copy Definite Columns from One Sheet to Another without Format

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

Copy Definite Columns from One Sheet to Another without Format

Read More: How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)


Similar Readings


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.

Excel VBA Copy Column to a New Sheet with Formula

Observe the steps below carefully.

STEPS:

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

Excel VBA Copy Column to a New Sheet with Formula

  • 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

Excel VBA Copy Column to a New Sheet with Formula

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.

Excel VBA Copy Column to a New Sheet with Formula

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

Excel VBA Copy Column to a New Sheet with Formula

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

Excel VBA Copy Column to a New Sheet with Formula

  • You can use this example for multiple columns also.

Read More: Excel Formula to Copy Cell Value from Another Sheet


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: How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)


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.

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


Conclusion

We have demonstrated 6 examples 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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo