How do I copy only certain columns?

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 workshe

How do I copy only certain columns?

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.


Table of Contents hideDownload Practice Book6 Excel Macro Examples to Copy Specific Columns from One Worksheet to Another Worksheet1. Copy Certain Columns from One Worksheet to Another Worksheet Using Macro2. Macro to Copy Particular Columns to Another Sheet with Format3. Copy Definite Columns from One Sheet to Another without Format4. Excel VBA Copy Column to a New Sheet with Formula5. Macro to Copy Specific Columns to Another Sheet with AutoFit6. Copy Specific Columns Keeping Same Column WidthConclusion

Download Practice Book

Download the practice book here.Copy Specific Columns to Another Sheet.xlsm


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

Lets 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 selectModule.
  • Then, type the code in the Modulewindow.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 asCopy_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 Developertab.
Copy Certain Columns from One Worksheet to Another Worksheet Using Macro
  • After that, select Run from the Macrowindow.
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

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.

Lets 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 Applicationswindow.
Macro to Copy Particular Columns to Another Sheet with Format
  • Second, go to Insert and selectModule.
  • Third, type the code in the Modulewindow.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 Developertab.
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

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.

Lets 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 Modulewindow.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 Developertab.
Copy Definite Columns from One Sheet to Another without Format
  • Click Run from the Macrowindow.
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

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 selectModule.
  • Then, type the code in the Modulewindow.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 theDevelopertab and selectMacros.
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.

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.

Lets 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 selectModule.
  • 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 Developertab.
  • Select the desired code from the Macro window and Runit.
  • Finally, you will see results like below in the expected sheet.

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 Basicwindow.
  • Secondly, go to Insert and select Module.
  • Now, type the code in the Modulewindow.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 Runit.
  • We can also run any code by pressing the F5 key after saving it.
  • Finally, you will see the desired results in another worksheet.

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.

Video liên quan