Moving Records to the database with multiple rows

[rimmidi]

Hi,

I am looking for help in User Form kind of excel sheet, where the data is entered in multiple rows and columns needs to be moved to a database. In the database, the data has to be inserted at the bottom of the last previously entered row. Please help. sample sheet attached. Thanks in advance.

[rimmidi]

Any help ?

[thameem127]

Where you need this data transfer? I mean with in sheet or other sheet.

Thanks

Thameem.

[rimmidi]

In another sheet.
Thanks,

[alansidman]

Give this a try:

Sub CopyData()
Dim i As Integer
i = 4
i = i + 1
For i = 4 To 7
With Sheets("Sheet1")
    .Cells(i, 1).Copy Destination:=Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp).Offset(1, -2)
    .Cells(i, 2).Copy Destination:=Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp).Offset(1, -1)
    .Cells(i, 3).Copy Destination:=Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp).Offset(1)
    End With
    Next
    
For i = 4 To 7
With Sheets("sheet1")
    .Cells(i, 1).Value = ""
    .Cells(i, 2).Value = ""
    .Cells(i, 3).Value = ""
    End With
    Next
        
End Sub

[alansidman]

If in another sheet, change the destination in my code to the applicable sheet where I have "Sheet1". I assumed from your example that it was in the same sheet.

[rimmidi]

Thank you, Alan. It worked like a charm.

However, I have one more problem. The inputs that formula derived, hence it copies the formula to other rows and i can't see the values as they are meant for that cell. I see all fields as #NUM. So how do I copy only the values and not formulas ?

Thanks,

[alansidman]

Are you saying that you are putting formulas in the input cells A4:D7? I am confused by your request.

[rimmidi]

C4 to C7 is derived by formula in relative to A and B column inputs.

[thameem127]

Use Mr. Alan's code and add .value below

Sub CopyData()
Dim i As Integer
i = 4
i = i + 1
For i = 4 To 7
With Sheets("Sheet1")
    .Cells(i, 1).Copy Destination:=Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp).Offset(1, -2).value
    .Cells(i, 2).Copy Destination:=Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp).Offset(1, -1).value
    .Cells(i, 3).Copy Destination:=Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp).Offset(1).value
    End With
    Next
    
For i = 4 To 7
With Sheets("sheet1")
    .Cells(i, 1).Value = ""
    .Cells(i, 2).Value = ""
    .Cells(i, 3).Value = ""
    End With
    Next
        
End Sub

[rimmidi]

Thanks Thameem.

But i get Run time error :1004 by doing so. Error - Copy method of Range class failed.

[alansidman]

Instead of calculating in C4:C7, I suggest you put the formula in your destination cells and not in the input cells.

[rimmidi]

Hi Alan,

I don't need the formula in the destination.

I have used the formula to list the servers based on the PRODUCT and COMPONENTS. So it list the servers, the I like to move to the destination just the values alone. currently, it is moving with formulas.

Thanks,
Ranga

[JosephP]

Sub CopyData()

Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp).Offset(1, -2).Resize(4, 3).Value = Sheets("Sheet1").Cells(4, 1).Resize(4, 3).Value
    
Sheets("sheet1").Cells(4, 1).Resize(4, 3).Value = ""
        
End Sub

[rimmidi]

Joseph,

Your vba program worked well. Thanks a lot.

Thank you Alan, Thameem for your quick responses.

Regards,
Ranga

[JBeaucaire]

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.