Excel VBA Copy specific Column from different Worksheets

Question

I'm currently developing a monitoring Tool in Excel using VBA and encountered some difficulties when copying data.

Current Code:

Sub CopyID()
    'Copies entire Row of IDs from "Sheet 2" to main Sheet "Main Sheet"

    Dim lastCell As Long
    LastCell = Cells(Rows.Count,'Sheet 2':M).End(xlUp).Row

    'Missing here: Copy to Column 1 at Row 3!

    Sheets("Sheet 2").Columns(M).Copy Destination:=Sheets("Main Sheet").Columns(1)

End Sub

What it is supposed to do: Copy the Data of Sheet2_Column M starting at Row 2 to Main Sheet Colum A sarting at Row 3

Also, I don't know if this is possible yet, use a specific formula for the destination (Formular is: =LEFT(Data,10))

I am glad for any response to this as I'd like to learn how these "Copy Methods" work in detail and am happy for any tipps and tricks regarding these methods.

Edit// The Copy Part should work like this

Sheet 2 Contains a Colum that has a headercell and X cells with a value that has a similar format.

Example of the Sheet 2 Contents

This is a row in Sheet 2. I only need the first 10 digits of the content of the cells. Is it possible to include that as a formula similar to

=Left(Sheet 2:M2,10)

so it works like this:

"sheet 2" cell content: "1234567891_1_123X" copy to "main sheet" as "1234567891"

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-07-24T08:06:25

Define your source and destination worksheet. And range/column names bust be submitted as strings like "M".

Sub CopyID()
    'Copies entire Row of IDs from "Sheet 2" to main Sheet "Main Sheet"
    Dim WsSource As Worksheet
    Set WsSource = ThisWorkbook.Worksheets("Sheet 2")    

    Dim WsDestination As Worksheet
    Set WsDestination = ThisWorkbook.Worksheets("Main Sheet")    


    Dim lastRow As Long
    lastRow = WsSource.Cells(WsSource.Rows.Count, "M").End(xlUp).Row

    'Missing here: Copy to Column 1 at Row 3!

    WsSource.Range("M2:M" & lastRow).Copy Destination:=WsDestination.Range("A3")
End Sub

Edit:

To copy only the first 10 characters of each cell would need a process for each value:

Option Explicit

Public Sub CopyID()
    'Copies entire Row of IDs from "Sheet 2" to main Sheet "Main Sheet"
    Dim WsSource As Worksheet
    Set WsSource = ThisWorkbook.Worksheets("Sheet 2")

    Dim WsDestination As Worksheet
    Set WsDestination = ThisWorkbook.Worksheets("Main Sheet")

    Dim lastRow As Long
    lastRow = WsSource.Cells(WsSource.Rows.Count, "M").End(xlUp).Row 'Find last row in column M

    Dim ArrSource As Variant
    ArrSource = WsSource.Range("M2:M" & lastRow).Value 'read column m values into array

    Dim i As Long
    For i = 1 To UBound(ArrSource) 'process each value in the array
        ArrSource(i, 1) = Left$(ArrSource(i, 1), 10) 'keep only left 10 characters
    Next i

    WsDestination.Range("A3").Resize(UBound(ArrSource), 1).Value = ArrSource 'write array into destination
End Sub

Note .Resize(UBound(ArrSource), 1) defines the destination the same size as the array is that we want to insert.

Excel VBA Copy specific Column from different Worksheets

1 Answers