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...

 

[My Aswer Is This]

Now your doing this one sheet at a time I assume. We could write the script to do all sheets at the same time if you want but you did not ask for that.

You really do not need the function.
Try this script:

Sub CopyDataToDZLOG()
Dim NewRow As Long
NewRow = Sheets("DZ LOG").Cells(Rows.Count, "B").End(xlUp).Row + 1
If NewRow < 5 Then NewRow = 6
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

 

[traub86]

So I gave that one a shot and nothing happened...Am I doing something wrong?

 

[My Aswer Is This]

I test all my scripts.
Did you have values in the cells?

For example:

Worksheets("DZ LOG").Cells(NewRow, 2).Value = Worksheets("CHALK 1").Range("C18").Value

You must have values in the cells if you want them entered in other cells

The code should put your values someplace. They should go at least some place

So I gave that one a shot and nothing happened...Am I doing something wrong?

 

[My Aswer Is This]

You should have values in sheet named "CHALK 1"

Range("C18")
Range("E7")
Range("E4")

You must have some value in all three of these cells

Then they will be entered in sheet named: "DZ LOG"
They will go into Columns 2 5 and 6

 

[traub86]

Ok I started from scratch with VBA code and tested my previous code and that worked as before. Then I verified values were present in C18, E7, and E4 of each of the respective worksheets that need to be copied (Chalk 1, 2, 3 and 4). Then I ran the code and nothing happens: no data transfers to worksheet "DZ LOG", no error box, and no code errors.

 

[My Aswer Is This]

You know my script will only work on sheet named Chalk1

You indicated you wanted to put this same script into buttons on all the other sheets chalk2 chalk3 chalk4

Run My script from Chalk1 with values in the cells you mentioned and see if it works.

It works for me.

Now it will not get any values from chalk2 chalk3 chalk4

If it works with chalk1 we will have to work on it working from the other sheets.

 

[My Aswer Is This]

Put this script in each of these sheets:
Since you said you wanted a button on each of these sheets.
Chalk1
Chalk 2
Chalk3
Chalk 4

Sub MineNew()
Dim NewRow As Long
NewRow = Sheets("DZ LOG").Cells(Rows.Count, "B").End(xlUp).Row + 1
If NewRow < 5 Then NewRow = 6
Worksheets("DZ LOG").Cells(NewRow, 2).Value = Range("C18").Value
Worksheets("DZ LOG").Cells(NewRow, 5).Value = Range("E7").Value
Worksheets("DZ LOG").Cells(NewRow, 6).Value = Range("E4").Value
End Sub

 

[traub86]

So I did exactly as you said and still have the same issue: nothing's happening. I even double and triple checked that I had values in the cells and the script was fully copied and pasted.

 

[My Aswer Is This]

I want you to put this script into a brand new workbook and try it.

The only reason I can think is that the script is entering the values but they are being entered way down on the sheet where you do not see them.
Or do this:

Look on sheet named ("DZ LOG").
and look all the way down column "B" and see if you see the values.

You can click on Range("B1") and then press Control and down arrow and it will take you to first cell with text

See the script is looking for the last row in column "B" with data.

And enterers your new data in one row below