none
Excel 2013 - Store Data in another sheet adding a new line RRS feed

  • Question

  • Hello People,

    I'm trying to create a dashboard in excel to store some objects names. 

    So my ideia is to have a Button like "Saving Object" and the goal is adding the name that user introduces and save it in another sheet (Sheet 1).  When user introduces a new name then Macro save the data in Sheet1 but in another line. I don't know if I'm explaining very well so I leave a example above:

    Is possible to create this process?

    Thank you very much

    Friday, June 26, 2015 6:50 PM

Answers

  • This code will perhaps do what you're asking for:

    Private Sub cmdSaveObject_Click()
        Dim shS As Worksheet 'Source sheet
        Dim shD As Worksheet 'Destination sheet
        Dim strObject As String
        Dim rngO As Range
        Set shS = Worksheets("Introduction")
        Set shD = Sheets("Blad1")
        'always introducing a new object in A2 in sheet "Introduction"
        strObject = shS.Range("A2")
        'if the 'new' object is already in shD then nothing will happen
        'else the new introduced objectname will be inserted in the first empty cel
        'in column A from shD
        Set rngO = shD.Range("A:A").Find(strObject)
        If rngO Is Nothing Then
            Set rngO = shD.Range("A" & shD.Rows.Count).End(xlUp).Offset(1, 0)
            rngO = strObject
        End If
    End Sub
    

    Jan

    • Marked as answer by sw125 Saturday, June 27, 2015 9:50 AM
    Saturday, June 27, 2015 7:27 AM

All replies

  • Hi,

    I imagine: There are two worksheets. Suppose they are sheet1 and sheet2.
    Sheet1 has a button [Saving Object], and user input values in it and click the button, then values in sheet1 will be copied in sheet2, the save value, the same cell.
    Is it right? If right, it's very easy.

    Regards,
    • Edited by Ashidacchi Saturday, June 27, 2015 5:40 AM
    Saturday, June 27, 2015 5:39 AM
  • This code will perhaps do what you're asking for:

    Private Sub cmdSaveObject_Click()
        Dim shS As Worksheet 'Source sheet
        Dim shD As Worksheet 'Destination sheet
        Dim strObject As String
        Dim rngO As Range
        Set shS = Worksheets("Introduction")
        Set shD = Sheets("Blad1")
        'always introducing a new object in A2 in sheet "Introduction"
        strObject = shS.Range("A2")
        'if the 'new' object is already in shD then nothing will happen
        'else the new introduced objectname will be inserted in the first empty cel
        'in column A from shD
        Set rngO = shD.Range("A:A").Find(strObject)
        If rngO Is Nothing Then
            Set rngO = shD.Range("A" & shD.Rows.Count).End(xlUp).Offset(1, 0)
            rngO = strObject
        End If
    End Sub
    

    Jan

    • Marked as answer by sw125 Saturday, June 27, 2015 9:50 AM
    Saturday, June 27, 2015 7:27 AM