Results 1 to 3 of 3

Transfering data to two sheets, same code but on one sheet data is over written

Threaded View

  1. #1
    Registered User
    Join Date
    03-04-2020
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    2

    Transfering data to two sheets, same code but on one sheet data is over written

    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
    Last edited by Pepe Le Mokko; 03-04-2020 at 07:38 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transfer data from Front sheet to data storage sheets (help with code pse)
    By Potholes in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-17-2019, 02:24 AM
  2. Transfering data From a Master Sheet to differnt sheets.
    By DirkVS371 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2016, 09:29 AM
  3. Replies: 0
    Last Post: 01-19-2015, 03:31 PM
  4. Transfering data from a master sheet to different sheets
    By gerocha in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-10-2013, 10:29 AM
  5. Transfering data into multiple sheets in 1 workbook.
    By Steve-B in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2008, 11:11 AM
  6. Transfering data between tabs using code.
    By greenteafiend in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2008, 09:37 AM
  7. Transfering Data from Several Sheets to another
    By Adalan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-16-2006, 01:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1