VBA to move data to another sheet but on the next available line?

[DMO123]

Hi All,

i am trying to do a VBA to copy data from one sheet to another but on the next available line. when it pastes it should be as a value. once its done this it goes back to the other tab and delete the data its copied. for example:

i have data in sheet 1 the data range is A:I down to wherever is finishes. this needs to be copied and moved to the next sheet (sheet 2) find the next available line once pasted the VBA goes back to sheet 1 and deletes the data copied.

the macro i have been using is below but i have found i get stuck after the data is copied as it is looking for A41 (Range("A41").Select)

Sub Move_Billed_Cases()
'
' Move_Billed_Cases Macro
'


'
Application.Goto Reference:="R5C1"
Range("A6:I6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveSheet.Next.Select
Application.Goto Reference:="R5C1"
Selection.End(xlDown).Select
Range("A41").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A41").Select
ActiveSheet.Previous.Select
Application.Goto Reference:="R5C1"
Range("A6:H6").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A6").Select
End Sub


thank you!

 

[igold]

Ok, then try this...

Sub Move_Billed_Cases()


    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cArr As Variant
    Dim cRng As Range
    Dim lRow1 As Long, lRow2 As Long
    
    lRow1 = Cells(Rows.Count, 1).End(xlUp).Row
    lRow2 = ActiveSheet.Next.Cells(Rows.Count, 1).End(xlUp).Row + 1
    cArr = Range("A6:I" & lRow1)
    Set cRng = Range("A6:I" & lRow1)
    ws.Next.Range("A" & lRow2).Resize(UBound(cArr, 1), UBound(cArr, 2)) = cArr
    cRng.Delete
    Range("A6").Select
    
End Sub

 

[DMO123]

Works perfectly - thank you so much for your help!

 

[igold]

Well it took a little back and forth, but I am glad it is working for you. I was happy to help. Thanks for the feedback!