none
Macro to move data from one sheet to table and clear contents RRS feed

  • Question

  • What I am trying to do is create a macro that will copy and move data from a column of cells to the last row of a table and then clear the contents of the vertical cells. The column (entry form) is on one sheet and the table is on another sheet (database). I would like to put data in the entry form press the icon to enter the data from the entry form to the last row of the database. I know the code to clear the contents I just need help with the code to copy and move the data to the table. The example is below.  The entry form range is C18:C25, and the columns that the data will be moving to is in columns B:I.

    In the Entry form column B is the Headers of the Table, and column C is what the data that needs to be moved over to the table.

    Header 1 Data 1
    Header 2 Data 2
    Header 3 Data 3
    Header 4 Data 4
    Header 5 Data 5
    Header 6 Data 6
    Header 7 Data 7
    Header 8 Data 8

    Saturday, September 9, 2017 1:14 PM

Answers

  • Hi RCSmart01,

    please refer the code below.

    Private Sub CommandButton1_Click()
    Dim temp As String
    Dim rng, rng1 As Range
    Dim row, row1 As Range
    Dim cell, cell1 As Range
    Dim sht1, sht2 As Worksheet
        Set sht1 = Sheets(1)
        Set sht2 = Sheets(2)
        Set rng = sht1.Range("C18:C25")
    Dim lastrow As Long
        lastrow = sht2.Cells(sht2.Rows.Count, "B").End(xlUp).row + 1
    Dim x, y As Integer
        x = 0
        y = 0
        Set rng1 = sht2.Range("B" & lastrow & ":I" & lastrow)
            For Each row In rng.Rows
                  For Each cell In row.Cells
                    x = x + 1
                    'Debug.Print (cell.Value)
                    temp = cell.Value
                    y = 0
                            For Each row1 In rng1.Cells
                                               
                                y = y + 1
                                If x = y Then
                                    row1.Value = temp
                                    'Debug.Print (row1.Value)
                                    End If
                                             
                            Next row1
                Next cell
            Next row
            rng.Value = ""
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 15, 2017 7:34 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, September 11, 2017 2:14 AM
  • Hi RCSmart01,

    please refer the code below.

    Private Sub CommandButton1_Click()
    Dim temp As String
    Dim rng, rng1 As Range
    Dim row, row1 As Range
    Dim cell, cell1 As Range
    Dim sht1, sht2 As Worksheet
        Set sht1 = Sheets(1)
        Set sht2 = Sheets(2)
        Set rng = sht1.Range("C18:C25")
    Dim lastrow As Long
        lastrow = sht2.Cells(sht2.Rows.Count, "B").End(xlUp).row + 1
    Dim x, y As Integer
        x = 0
        y = 0
        Set rng1 = sht2.Range("B" & lastrow & ":I" & lastrow)
            For Each row In rng.Rows
                  For Each cell In row.Cells
                    x = x + 1
                    'Debug.Print (cell.Value)
                    temp = cell.Value
                    y = 0
                            For Each row1 In rng1.Cells
                                               
                                y = y + 1
                                If x = y Then
                                    row1.Value = temp
                                    'Debug.Print (row1.Value)
                                    End If
                                             
                            Next row1
                Next cell
            Next row
            rng.Value = ""
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 15, 2017 7:34 AM
    Moderator