Using VBA to copy specific data from sheet1 to another sheet in successive order

[traub86]

I am new to this and have exhausted my search resources. I have a workbook with 5 total sheets. 1-4 have an identical template with potentially varying data and sheet 5 captures this specific data in a row format. Sheets 1-4 all have their own VBA modules through a macro button. When I click the button it works perfectly, however I will need to click that button again without overwriting log info from the first click. I just need data to be logged in successive order until a) I am done using the template or b) I run out of available rows. Here is the vba code I am currently using:

Sub CopyDataToDZLOG()
Dim NewRow: NewRow = GetNextEmptyRowOnDZLOG
Worksheets("DZ LOG").Cells(NewRow, 2).Value = Worksheets("CHALK 1").Range("C18").Value
Worksheets("DZ LOG").Cells(NewRow, 5).Value = Worksheets("CHALK 1").Range("E7").Value
Worksheets("DZ LOG").Cells(NewRow, 6).Value = Worksheets("CHALK 1").Range("E4").Value

End Sub


Function GetNextEmptyRowOnDZLOG() As Integer
Dim RowCount: RowCount = 6
Do
RowCount = RowCount + 1
Loop Until IsEmpty(Worksheets("DZ LOG").Cells(RowCount, 20).Value)
GetNextEmptyRowOnDZLOG = RowCount


End Function



Please help...

 

[traub86]

Yep that was it. All the times I hit the run button the entries were at the bottom starting on row 72. So now we're getting somewhere...

 

[My Aswer Is This]

Well thats great. Let me know if you need more help.

Yep that was it. All the times I hit the run button the entries were at the bottom starting on row 72. So now we're getting somewhere...