Excel - Macro to copy a dynamic row number from one workbook to another

I have tried searching for an answer to this, so far I have had no luck.

The solution I am looking for is regarding two workbooks and being able to copy one row from the source workbook (it has 1000 rows) to another book (to only contain one row at any one time, not including column headers).

The other issue is that the next time the macro is run I need to look at the next row down in the source workbook e.g.

First run copies row 2 (as column headers are in row 1) into row 2 of the other book Second run copies row 3 into row 2 of the other book (as the program reading this book only looks in r开发者_StackOverflowow 2).

So I assume a counter is needed in the code?

If there are any suggestions it would be very much appreciated.

---

You will need a macro to copy the data over and since you are going across macro runs (one row each time the macro is run), you will need to store the value of the row you last copied somewhere outside the macro. Let's call this LastRowCopiedOver

You can store this in one of a few places:

• A hidden and protected sheet in the same workbook
• A protected cell in one of the existing workbooks

Every time the macro is run successfully, you will update the value for LastRowCopiedOver so that you know what row to pick the next time around.

When you reach the end of the book, you can display a messagebox with a Yes/No question to reset back to row 2.

---

A Static Function will do the trick.

Static Function RowCounter() As Long
    Dim i ' Value of locally declared variable is preserved between calls.
    i = i + 1
    RowCounter = i
End Function

Each time you call it from your main program, i will increment by 1. Since RowCounter is static, it will remember the value of i is between calls.

Run the following sub several times and watch the MsgBox increment. The current row will be copied to "Sheet2". Replace that with whatever your destination sheet is.

Sub main()
    Dim iRow As Long

    Do While iRow < 2 ' To skip headers row 1...
        ' Get next row number.
        iRow = RowCounter
    Loop
    MsgBox iRow ' To show that each time main is called, iRow will be incremented by 1.

    ' Copy and paste that row...
    Rows(iRow).Copy Destination:=Worksheets("Sheet2").Rows(2)

End Sub

You can reset the counter to zero by going in the VBA editor and pressing the reset button (the square) on the Standard toolbar or in the Run menu.

If you save the counter in some protected/hidden cell or sheet, resetting it will be a PITA.

---

Thanks for the input, I spent a bit of time on a solution for this after reading the solutions above. I ended up creating a separate excel file, I then used some VBA script to create a counter and then copy one line from the large excel source file to the new file so it only contains one row of data.

I have now amended my QAWP script to run this excel macro and it now sets the data in the script using the new single row spreadsheet.

This seems to be working so far, and it means that I can create new test data separately from the datasheet being used by QAWP.

I have posted the excel VBA script below for reference:

Sub Copy_Next_Record()

Application.Workbooks.Open _
    "C:\Documents and Settings\user\My Documents\Personal_Data.xls"
Dim RowCount As Range
Set RowCount = _
    Workbooks("Full_Personal_Data.xls").Worksheets("Count").Range("A1")   
Workbooks("Full_Personal_Data.xls").Worksheets("Data").Activate   
Rows(RowCount).Copy _
    Destination:=Workbooks("Personal_Data.xls").Worksheets("Sheet1").Rows(2)    
RowCount.Value = RowCount.Value + 1    
Workbooks("Personal_Data.xls").Close True  
Workbooks("Full_Personal_Data.xls").Close True

End Sub