Hi,
I wonder if somebody may have a smart solution to my delicate problem. I have in my excel doc 3 different sheets. One for input of data and with one coded commandbutton. When I fill in data in the fields my intention is to move the data over to two different sheets, one which will be my report and one for historic data. Purpose later on is that for the report I don't want to put in all data and that's why I transfer it to two sheets.
My problem:
After putted in data and pressed the buttom it is tranferred over to my two sheets. It works perfectly fine 1st and 2n time, data moved and putted into a row below the first one, however on the third time which is supposed to go into "historic data" is over writing the previous row. Code which tells where to put in the data is identical. I am definitly not an expert but I have tried with changing target sheet/cell for the code failing and cutting out the working code.
I would like to have these two "functions" with same data transferred to two sheets in one buttom, user-friendly, but if someone have a better suggestion please tell.
Example of code -->
Private Sub CommandButton1_Click()
'Declare data type
Dim ServiceOrder As String, PerformedDate As String, Technology As String, PerformedBy As String, TimeCode As String
Dim Description As String, ClientRef As String, ReportedHours As String, TechnicalFactor As String
'Seting info on where to find data to transfer, sheet and cell.
Worksheets("Input").Select
ServiceOrder = Range("A13")
PerformedDate = Range("B13")
Technology = Range("C13")
PerformedBy = Range("D13")
TimeCode = Range("E13")
Description = Range("F13")
ClientRef = Range("G13")
ReportedHours = Range("H13")
TechnicalFactor = Range("I13")
'Code for transfering data, setting sheet and start cell, in addition it also make a check on which row that is empty
With Worksheets("Historic data").Select
Worksheets("Historic data").Range("A18").Select
If Worksheets("Historic data").Range("A18").Offset(1, 0) <> "" Then
Worksheets("Historic data").Range("A18").End(xlDown).Select
End If
'Following code decides where the input data will be put in the target sheet, with cell A18 to start.
'It starts with (1,0) in cell A18 and next one (0,1) tells the system to put data 1 step to the right of previous.
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = PerformedDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Technology
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = PerformedBy
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TimeCode
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Description
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ClientRef
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ReportedHours
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TechnicalFactor
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ServiceOrder
End With
'Transfering data to sheet "Report"
With Worksheets("Report").Select
Worksheets("Report").Range("A18").Select
If Worksheets("Report").Range("A18").Offset(1, 0) <> "" Then
Worksheets("Report").Range("A18").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = PerformedDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Technology
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = PerformedBy
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TimeCode
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Description
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ClientRef
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ReportedHours
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = TechnicalFactor
ActiveCell.Offset(0, 3).Select
ActiveCell.Value = ServiceOrder
End With
'Code that tells the system to clear the cells for data and ("A13:I13) is the range of cells to be cleaned
Worksheets("Input").Select
Worksheets("Input").Range("A13:I13").ClearContents
End Sub
Bookmarks