I can't get my data transferred and jump to a new row or recall data

**TBV** · 03-31-2021, 03:05 AM

Hello,

I am trying to make a database and have the following requirements:

I have a sheet mastertemp, here I want to enter all my NCR data.
I also have a sheet overview, this where I want my data to go.

on the mastertemp sheet:
I want to work with buttons so I made a button to add the date: Range("E9").Value = Now()

I also want the data I entered to be exported to the overview sheet and clearing the temp for new data. at the same time I want it the row on the overview temp to jump to a new line so I can store new data.
here is also one of my problems.

It doesn't work and I don't know how to fix this, this is what I put in:

Private Sub CommandButton4_Click()
Sheets("mastertemp").Range("E7").Copy Destination:=Sheets("overview").Range("A2")
Sheets("mastertemp").Range("E8").Copy Destination:=Sheets("overview").Range("B2")
Sheets("mastertemp").Range("E9").Copy Destination:=Sheets("overview").Range("C2")
Sheets("mastertemp").Range("E10").Copy Destination:=Sheets("overview").Range("D2")
Sheets("mastertemp").Range("E11").Copy Destination:=Sheets("overview").Range("E2")
Sheets("mastertemp").Range("E15").Copy Destination:=Sheets("overview").Range("H2")
Sheets("mastertemp").Range("E27").Copy Destination:=Sheets("overview").Range("F2")

Sheets("overview").Range("A2").Offset(1, 0).Select
Sheets("overview").Range("B2").Offset(1, 0).Select
Sheets("overview").Range("C2").Offset(1, 0).Select
Sheets("overview").Range("D2").Offset(1, 0).Select
Sheets("overview").Range("E2").Offset(1, 0).Select
Sheets("overview").Range("F2").Offset(1, 0).Select
Sheets("overview").Range("H2").Offset(1, 0).Select

Application.CutCopyMode = False

'Updateby Extendoffice
Range("E7").ClearContents
Range("E8").ClearContents
Range("E9").ClearContents
Range("E10").ClearContents
Range("E11").ClearContents
Range("E15").ClearContents
Range("E27").ClearContents

MsgBox "NCR data transported"

End Sub

The last button I made was for a new number that I already have in the next row:2101,2102,2103 to be visible on E7 of the mastersheet so I can fill in a new report under the next number and save it.
I don't know if this one is not working because of the other button.. but also this one I can't get to work.

Private Sub CommandButton2_Click()
Sheets("overview").Range("A2").Copy Destination:=Sheets("mastertemp").Range("E7")
ActiveCell.Offset(1).Select

So can someone please help me and explain to me what I am doing wrong?

**Joske920** · 03-31-2021, 09:22 AM

hi,

give the folowwing a look

Sub AddToOverview()
   Dim wsOverview As Worksheet, emptyRowOverview As Long
   
   Set wsOverview = Worksheets("Overview")
   
   emptyRowOverview = wsOverview.Cells(Rows.Count, "B").End(xlUp).Row + 1 'Give the rownumber of the first empty row
   
   wsOverview.Cells(emptyRowOverview, "A") = Me.Range("E7")
   wsOverview.Cells(emptyRowOverview, "B") = Me.Range("E8")
   wsOverview.Cells(emptyRowOverview, "C") = Me.Range("E9")
   wsOverview.Cells(emptyRowOverview, "D") = Me.Range("E10")
   wsOverview.Cells(emptyRowOverview, "E") = Me.Range("E11")
   wsOverview.Cells(emptyRowOverview, "F") = Me.Range("E15")
   wsOverview.Cells(emptyRowOverview, "G") = Me.Range("E27")
   
   EmptyInputFields
   MsgBox "NCR data transported"
   
End Sub

Sub EmptyInputFields()
   Me.Range("E7").ClearContents
   Me.Range("E8").ClearContents
   Me.Range("E9").ClearContents
   Me.Range("E10").ClearContents
   Me.Range("E11").ClearContents
   Me.Range("E15").ClearContents
   Me.Range("E27").ClearContents
End Sub

Sub GetNextNumber()
   Dim wsOverview As Worksheet, emptyRowOverview As Long
   
   Set wsOverview = Worksheets("Overview")
   emptyRowOverview = wsOverview.Cells(Rows.Count, "B").End(xlUp).Row + 1 'Give the rownumber of the first empty row
   
   Me.Cells(7, "E") = wsOverview.Cells(emptyRowOverview, "A")
End Sub