Copy values from one sheet to the other

**benedictexcel** · 01-20-2012, 04:34 PM

Hi everyone
I know the title sounds a little basic but actually it's a big problem I have.
I am creating a work control excel workbook. I want a worksheet people will enter values everyday and another one that will compile the values.
That way, the user will enter the date, the required informations that are compiled on the other sheet so the next he can changes the date, the fields clear themselves, enter the new informations that are copied next to the ones from the day before in the compilation sheet.
I don't think I need to write a macro in vba in order to be able to do this but if needed I don't fear doing it. My problem is I have a hard time figuring how to process.
Have anyone ever done something like that? Do you have hint and tips about how I can do that?
Thanks for helping!

Bénédict

**ExcelIsEasy** · 01-21-2012, 04:45 AM

Hello Bénédict!

You should use data validations for your people and copy special only values to your sheet. I would do it if I were you.

**arlu1201** · 01-21-2012, 11:47 AM

Do you have a sample book that you could attach so we could assist you better?

**benedictexcel** · 01-23-2012, 10:02 AM

Hi here's a workbook that will give you an idea about what I'm trying to do. On the fist sheet, the worker enters the date, the projects and the resources and quantities. On the second sheet, data is compiled for everyday. So, If the worker saves, the data is on 2places at the same and when he comes back and enter the values for the next day, they're entered next to those from the first day on the second sheet(see attchment day1 and day2). I'm thinking I will need a macro in order to do that but still I don't know how to proceed.

Thanks for your help

Bénédict

the attachedd files are a really simple representation of what I'm doing, but I believe it gives a good idea about what I want to do. I can't show the real thing on the web.

**arlu1201** · 01-25-2012, 09:52 AM

Use this code -

Option Explicit
Dim lrow As Long
Dim i As Long
Dim j As Long
Dim lastrow As Long

Sub compile_data()

Application.ScreenUpdating = False

With Worksheets("control")
    lrow = Worksheets("work").Range("C" & Rows.Count).End(xlUp).Row
    For i = 6 To lrow
        For j = 4 To 6
            If Worksheets("work").Range("G" & i).Value <> "" Then
                lastrow = .Range("B" & Rows.Count).End(xlUp).Row
                .Range("B" & lastrow + 1).Value = Worksheets("work").Range("D3").Value
                .Range("C" & lastrow + 1).Value = Worksheets("work").Range("D2").Value
                .Range("D" & lastrow + 1).Value = Worksheets("work").Cells(4, j).Value
                .Range("E" & lastrow + 1).Value = Worksheets("work").Cells(i, 3).Value
                .Range("F" & lastrow + 1).Value = Worksheets("work").Cells(i, j).Value
            End If
        Next j
    Next i
    
End With

With Worksheets("work")
    For i = 6 To lrow
        For j = 4 To 6
            .Cells(i, j).Value = ""
        Next j
    Next i
End With

With Worksheets("control")
    lrow = .Range("B" & Rows.Count).End(xlUp).Row
    For i = lrow To 3 Step -1
        If .Range("F" & i).Value = "" Then
            .Rows(i & ":" & i).Delete
            lrow = lrow - 1
        End If
    Next i
End With

Application.ScreenUpdating = True

End Sub