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]

Does this do what you want...

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.ClearContents
    Range("A6").Select
    
End Sub

 

[DMO123]

Hi @igold this works perfectly but how do i stop it from copying the empty rows as well?

thanks!

 

[igold]

Are you saying that there are blank rows in your source data prior to copying it to the new page...

 

[DMO123]

Yes, so say the data finishes on line 100 below the 100 i an additional 100 lines empty (which will always be there as data is moved around).

 

[igold]

You may have to explain to me again. The way is the code is written, it looks for the last row with data in column "A" on the source page and copies from the first row to that row. Where are these 100 lines you are talking about.

If there are blank rows after the last row with data the code should not be copying that...

 

[DMO123]

Sure no worries. in sheet 1 i have a table that goes down to line 100. only 30 of these lines are taken with information. in the original code it looks for all cells from A:I and copies all cells with information in. the code then move to the next tab (sheet 2 in this case) finds column A and looks for the last available row paste the 30 rows with information in from sheet 1. the code then goes back and deletes the information in A:H in the 30 cells containing information. at the moment the code seems to copy the extra 70 rows that are left with no information in, because of this when it come to copy the next lot (as this is a button vba) there is a 70 row gap between each.

i hope this clarify s the issues im facing.

 

[igold]

Ok, if I am understanding you, you only want to take the first 30 rows of data on the source sheet, copy them to the second sheet and then delete those 30 rows from the source sheet. If this is right then how about this code:

Sub Move_Billed_Cases()


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

If this is not right, let me know and we will take another shot at it...

 

[DMO123]

@igold the number of rows that contains information can very it could be 30 or it could be 500 but due to the sort that happens when the data is in that sheet i will always end up with extra rows underneath the data with nothing in. i need to leave blank rows behind and only take rows that has data in columns A:I

thanks!

 

[DMO123]

I thought i would add more information with an example table, please see below:

so for example the table will look like the below (the number of rows containing data can vary in this case i populated 4 lines:

A	B	C	D	E	F	G	H	I	what to do with each row?
Data	Data	Data	Data	Data	Data	Data	Data	Data	Copy This Line
Data	Data	Data	Data	Data	Data	Data	Data	Data	Copy This Line
Data	Data	Data	Data	Data	Data	Data	Data	Data	Copy This Line
Data	Data	Data	Data	Data	Data	Data	Data	Data	Copy This Line
									Leave Line Behind
									Leave Line Behind
									Leave Line Behind
									Leave Line Behind
									Leave Line Behind

<tbody>
</tbody>

thanks!