VBA - Move rows with data + first empty row from Sheet1 -- And insert them above other rows in Sheet2.

[LisaExcel]

Hi all!

I'm stuck!

I'm trying to move all rows with data in them + the first empty row below it from Sheet1.
I then want to insert all those rows above the rows that's already on Sheet 2.

Anyone?

 

[psgoldberg]

To get unstuck, you would need to provide more information. Are you just trying to do it with regular copy/paste commands. Do you want to do it automatically; if, so, under what conditions. As good as this forum is, mind reading is a little too far a step.

 

[LisaExcel]

To get unstuck, you would need to provide more information. Are you just trying to do it with regular copy/paste commands. Do you want to do it automatically; if, so, under what conditions. As good as this forum is, mind reading is a little too far a step.

Oh sorry...
I meant that on Sheet1 from row 2 the macro should mark all rows with data in them plus an extra row. Then move those rows to Sheet2 and insert them above row 2 on that sheet.
I don't know how to explain it better...

 

[psgoldberg]

Your explanation still leaves a lot to the imagination so here is an attempt at deciphering it.
If you were to just copy the data to the bottom of the target table, it is simpler..... Your data to be copied is in a table named tblSource and the target is named tblTarget

Sub CopyData()
Dim tblSource As ListObject, tblTarget As ListObject
Dim HowManyRowsToInsert As Long
Dim AferWhichRowToInsertRow As Long
    Set tblSource = ActiveWorkbook.Worksheets("Sheet1").ListObjects("tblSource")
    Set tblTarget = ActiveWorkbook.Worksheets("Sheet2").ListObjects("tblTarget")

    HowManyRowsToInsert = tblSource.ListRows.Count + 1 'the +1 gives the extra blank row (which makes no sense)

    AferWhichRowToInsertRow = 0  '0 will be the first row
    For i = 1 To HowManyRowsToInsert
        tblTarget.ListRows.Add (AferWhichRowToInsertRow + i)
    Next
    tblSource.DataBodyRange.Copy
    tblTarget.DataBodyRange(1, 1).PasteSpecial
    Application.CutCopyMode = False
End Sub

 

[LisaExcel]

Your explanation still leaves a lot to the imagination so here is an attempt at deciphering it.
If you were to just copy the data to the bottom of the target table, it is simpler..... Your data to be copied is in a table named tblSource and the target is named tblTarget

Sub CopyData()
Dim tblSource As ListObject, tblTarget As ListObject
Dim HowManyRowsToInsert As Long
Dim AferWhichRowToInsertRow As Long
    Set tblSource = ActiveWorkbook.Worksheets("Sheet1").ListObjects("tblSource")
    Set tblTarget = ActiveWorkbook.Worksheets("Sheet2").ListObjects("tblTarget")

    HowManyRowsToInsert = tblSource.ListRows.Count + 1 'the +1 gives the extra blank row (which makes no sense)

    AferWhichRowToInsertRow = 0  '0 will be the first row
    For i = 1 To HowManyRowsToInsert
        tblTarget.ListRows.Add (AferWhichRowToInsertRow + i)
    Next
    tblSource.DataBodyRange.Copy
    tblTarget.DataBodyRange(1, 1).PasteSpecial
    Application.CutCopyMode = False
End Sub

Hi!

Thanks a million, but it doesn't work...

I get this:

I want to move these rows...

...to here:

 

[psgoldberg]

a) Did you name your tables as tblSource and tblTarget. It looks from the debugger that you did not. (How to Name a Table in Microsoft Excel)
b) Did you download the file I posted which does work exactly as you describe.
c) put your file in Dropbox, etc. and we can "debug"

 

[LisaExcel]

Hi!
I can't use Dropbox due to security reasons.
Anyways, I think I'll try to find this out.
Many thanks for your help.