How To Copy Specific Columns In Excel Using Vba

How to Copy Specific Columns in Excel Using VBA

VBA (Visual Basic for Applications) is a programming language used in Microsoft Office applications like Excel to automate tasks. To copy specific columns in Excel using VBA, you can use the following code:

    Sub CopySpecificColumns()
        Dim sourceSheet As Worksheet
        Dim destinationSheet As Worksheet
        Dim sourceRange As Range
        Dim destinationRange As Range
        Dim columnToCopy As Integer
        
        Set sourceSheet = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the name of your source sheet
        Set destinationSheet = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to the name of your destination sheet
        columnToCopy = 2 ' Change 2 to the column number you want to copy
        
        ' Find the last row in the source sheet
        lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, columnToCopy).End(xlUp).Row
        
        ' Define the source range
        Set sourceRange = sourceSheet.Range(sourceSheet.Cells(1, columnToCopy), sourceSheet.Cells(lastRow, columnToCopy))
        
        ' Define the destination range
        Set destinationRange = destinationSheet.Cells(1, 1) ' Change the destination cell as per your requirement
        
        ' Copy the values from the source range to the destination range
        sourceRange.Copy destinationRange
    End Sub
  

Let’s go through the above code:

1. Set sourceSheet = ThisWorkbook.Sheets("Sheet1") sets the source sheet to be copied from. Replace “Sheet1” with the name of your source sheet.
2. Set destinationSheet = ThisWorkbook.Sheets("Sheet2") sets the destination sheet where the columns will be copied. Replace “Sheet2” with the name of your destination sheet.
3. columnToCopy = 2 sets the column number to be copied. Change 2 to the column number you want to copy.
4. lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, columnToCopy).End(xlUp).Row finds the last row in the source sheet for the specified column to copy.
5. Set sourceRange = sourceSheet.Range(sourceSheet.Cells(1, columnToCopy), sourceSheet.Cells(lastRow, columnToCopy)) defines the source range within the specified column.
6. Set destinationRange = destinationSheet.Cells(1, 1) sets the destination range where the copied values will be pasted. Change the destination cell as per your requirement.
7. sourceRange.Copy destinationRange copies the values from the source range to the destination range.

By running this VBA macro, the specified column from the source sheet will be copied and pasted into the destination sheet.

Example:

Consider you have an Excel workbook with two sheets named “Source” and “Destination”. The “Source” sheet has data in column B (starting from B1) that you want to copy to the “Destination” sheet.

Source Sheet:

Destination Sheet (before running the macro):

After running the macro, the “Destination” sheet will be updated as follows:

This example demonstrates how the specified column (B) from the source sheet gets copied and pasted into the destination sheet using VBA.