Looking for a blank row & repeating tasks

    Hi


    I looking for some help piecing together some macro as well as some extra code that I’m missing.


    I currently have a worksheet (A) that is gathering it’s information from another worksheet (B) from the same workbook.


    I need (A) to print then to go to (B), clear the top row, copy row 2 and paste into row 1 – (which will update (A)),delete row 2 to move the list up, then print the new results in (A). This needs to continue until it finds an empty row in (B) then it should stop.


    Does that make sense?!?


    I can write a macro for the copy/paste/delete and print, but do not know how to repeat the macro until the blank row is found in (B).


    Thanks for reading


    Bertie

    Hi,


    try to use a loop function, something like this:


    'Do until row 2 of book B is "" (assuming when you start macro you are in workbook B)


    Do While ActiveSheet.Cells(2, 1).Value = ""
    Windows("BookA").Activate
    'print the stuff
    Windows("BookB").Activate
    'Clear row

    'Copy row

    'Delete row
    Loop


    (Just add a check if a cell from row2 is empty=> no more records)


    Gollem

    Thanks Gollem


    Will give that a go....however could you or anyone tell me why I'm getting a 'runtime error 1004' for the below? IT has a problem with 'Rows("1").Select' , I've also tried Rows("1:1").Select, but no joy. Apologies but I only have a basic understandering.


    Thanks
    Bertie


    Private Sub CommandButton1_Click()
    Range("A1:N37").Select
    Selection.PrintOut Copies:=1, Collate:=True
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    Sheets("Delivery Data").Select
    Rows("1").Select
    Selection.ClearContents
    Rows("2").Select
    Selection.Copy
    Rows("1").Select
    ActiveSheet.Paste
    Rows("2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Sheets("Delivery Note").Select
    End Sub

    Hi,


    you don't need to SELECT


    Private Sub CommandButton1_Click()
    Range("A1:N37").Select
    Selection.PrintOut Copies:=1, Collate:=True
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    With Sheets("Delivery Data")
    .Rows("1").ClearContents
    .Rows("2").Copy destination:=.Rows("1")
    Application.CutCopyMode = False
    .Rows("2").delete
    End With
    Sheets("Delivery Note").Select
    End Sub


    hope it works

    Thanks Jindon


    Your part works fine but when I try to combine with the Do...Loop that gollem suggested I have problems. I changed 'Do while..' to 'Do until..',(but both had the same error message) would this be a problem, I'm being told Loop without Do (?) - also the command button needs to be on Worksheet A not B which is why I'm trying to activate a sheet before starting the loop, not sure if thats a problem.


    Any ideas?? Code below..


    Private Sub CommandButton1_Click()
    Sheets("Delivery Data").Select
    Do Until ActiveSheet.Cells(1, 1).Value = ""
    Sheets("Delivery Note").Select
    Range("A1:N37").Select
    Selection.PrintOut Copies:=1, Collate:=True
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    With Sheets("Delivery Data")
    .Rows("1").ClearContents
    .Rows("2").Copy Destination:=.Rows("1")
    Application.CutCopyMode = False
    .Rows("2").Delete
    Sheets("Delivery Note").Select


    Loop
    End Sub


    Thanks again


    Bertie

    I'm really not sure if it works,


    Private Sub CommandButton1_Click()


    Do Until Sheets("Delivery Data").Cells(1, 1).Value = ""
    Sheets("Delivery Note").Range("A1:N37").PrintOut Copies:=1, Collate:=True
    With Sheets("Delivery Data")
    .Rows("1").ClearContents
    .Rows("2").Copy Destination:=.Rows("1")
    Application.CutCopyMode = False
    .Rows("2").Delete
    End With
    Loop
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login