CODEWITHSUNDEEP
vbaexcel
user10051952
user10051952

Reputation:

Excel VBA Copy specific Column from different Worksheets

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

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"

Upvotes: 1

Views: 169

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

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.

Upvotes: 0

Related Questions