copy till last used row of a column and paste it into another column of another sheet

[yogi_himalayan]

Every week I get data with random number of rows and I need to rearrange those data in certain order so it can accommodate with our system.

I have attached spreadsheet and would appreciate your assistance with macro to address following issue

1. need to find last used row and copy column I on sheet("internal") and paste to A2 in sheet("Int-SendOut")
2. need to find last used row and copy column H on sheet ("Internal") and paste to B2 in sheet("Int-SendOut")
3. need to find last used row and copy column C on sheet("Internal") and paste to C2 in sheet(Int-Sendout")

I am wondering what would be vba code to achieve the result based on last used row.

Thank you very much in advance for your assistance.

[Trebor76]

Hi there,

Try this:

Option Explicit
Sub Macro1()
    
    Sheets("Int-SendOut").Range("A2").Value = Sheets("Internal").Range("I" & Rows.Count).End(xlUp).Value
    Sheets("Int-SendOut").Range("B2").Value = Sheets("Internal").Range("H" & Rows.Count).End(xlUp).Value
    Sheets("Int-SendOut").Range("C2").Value = Sheets("Internal").Range("C" & Rows.Count).End(xlUp).Value

End Sub

Regards,

Robert

[yogi_himalayan]

Hi Robert,

Thanks. I use the above code but it is only returning value on row 2 of sheets("Int-SendOut"). Is there any way we can get all data till last used row from sheets("Internal") and have them pasted on sheets("Int-SendOut")

Kind regards

[Trebor76]

Oh, OK. Try this:

Option Explicit
Sub Macro2()

    Const lngStartRow As Long = 2 'Starting (static) row number for the data. Change to suit (if necessary).

    Dim lngMyRow As Long
    Dim varMyCols As Variant
    Dim strMyCols() As String
    
    Application.ScreenUpdating = False
    
    For Each varMyCols In Array("I-A", "H-B", "C-C") '<= Columns to be copied from - to
    
        strMyCols() = Split(CStr(varMyCols), "-")
        
        lngMyRow = Sheets("Internal").Range(strMyCols(0) & Rows.Count).End(xlUp).Row
    
        Sheets("Internal").Range(strMyCols(0) & lngStartRow & ":" & strMyCols(0) & lngMyRow).Copy Destination:=Sheets("Int-SendOut").Range(strMyCols(1) & lngStartRow)
        
    Next varMyCols
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

[yogi_himalayan]

Thank you Robert. Such a nice and perfect code I would have never learnt on my own. I was never aware that we can use copy paste array as you have used. Much appreciated your time and effort.

Kind regards

[mangesh.mehendale]

hii

Try this ...

Sub copytlllst()
    Dim lst As Long
    Dim lst2 As Long
    Dim lst3 As Long
    Dim intr As Worksheet
    Dim insnot As Worksheet
    Set intr = Worksheets("Internal")
    Set insnot = Worksheets("Int-SendOut")
    With intr
        lst = .Cells(.Rows.Count, "I").End(xlUp).Row
        lst2 = .Cells(.Rows.Count, "H").End(xlUp).Row
        lst3 = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    Worksheets("Internal").Select
        Range("I2:I" & lst).Select
        Selection.Copy
    Worksheets("Int-SendOut").Select
    With insnot
        Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    End With
    Worksheets("Internal").Select
        Range("H2:H" & lst2).Select
        Selection.Copy
    Worksheets("Int-SendOut").Select
    With insnot
        Range("B2").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    End With
    Worksheets("Internal").Select
        Range("C2:C" & lst).Select
        Selection.Copy
    Worksheets("Int-SendOut").Select
    With insnot
        Range("C2").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    End With
End Sub

[yogi_himalayan]

Thank you Mangesh for your time and effort. I have used Robert's code as it is short and sweet.

[Trebor76]

Thank you Robert. Such a nice and perfect code I would have never learnt on my own. I was never aware that we can use copy paste array as you have used. Much appreciated your time and effort.

You're welcome. Always extra happy to help a fellow Aussie

[mangesh.mehendale]

Hii Trebor76,
What is this coding nice ...

Let me know about

varMyCols In Array("I-A", "H-B", "C-C")

[Trebor76]

Hi Trebor76,
What is this coding nice ...

Let me know about
varMyCols In Array("I-A", "H-B", "C-C")

These are the columns the data is being copied from and to. For instance, column I to column A fro the first block. The varMyCols variable picks up each set of columns which are then split by the minus symbol "-" in the strMyCols array.

HTH

Robert

[mangesh.mehendale]

Quit hard because I am beginner .... Thanks...