Slight Editing of Existing Macro Required

Hello,

I have this excel spreadsheet with a macro function. I need slight modification in it and I can't figure out how to do it. In the attached excel file there are two spreadsheets. On the first sheet, there is a button "Save Data" which when clicked sends relevant data(C4 & K11-K16) from spreadsheet 1 to spreadsheet 2, Row 5(C5-I5) (try it for yourself for understanding it). When the "Save Data" button is clicked again, the data is transferred again but this time on Row 6 (C6-I6).

Now I have added two more things "Material" and "Weight" to the original spreadsheet. So now I want these two data to be transferred along with the previous data. Now what I want is that when I click "Save Data" the data should transfer as before but this time along with the previous data, values entered in spreadsheet 1 cells F2 & F4 should also be transferred to spreadsheet two in the cells D5(D5 onwards with every click on "save data" button) and E5(E5 onwards).

So now 9 values should be transferred from spreadsheet 1 to spreadsheet 2. That is values from C4, F2, F4, K11-K16 should be transferred to row 5 (C5-K5) in spreadsheet 2 on the first click. When clicked again the values should this time be transferred to row 6(C6-K6) in spreadsheet 2.

Seeing the excel file will make things very clear for you.

Thanks, Any help will be appreciated.

See attached

Almost there .. But the values from F2 and F4 in sheet 1 should be transferred to D5 and E5 in Sheet 2 respectively. In the file you attached it is being transferred to J5 and K5 instead.

Sub Paste_Component()

    Application.ScreenUpdating = False

    Dim cName As String
    cName = Range("C4").Value
    Dim opRange As Range
    Dim opt1rng As Range
    Dim opt2rng As Range
    Set opRange = Range("K11:K16")
    Set opt1rng = Range("F2")
    Set opt2rng = Range("F4")

    Sheet2.Select
    Dim sPoint As String
    sPoint = Range("C5").Address(False, False)
    Range(sPoint).Select
    
    Do Until ActiveCell = vbNullString
        If ActiveCell = vbNullString Then
            ActiveCell.Select
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop
    Range("D5").Value = opt1rng
    Range("E5").Value = opt2rng
    ActiveCell = cName
    ActiveCell.Offset(0, 3).Select
    opRange.Copy
    Selection.PasteSpecial Paste:=xlValues, Transpose:=True
    Range(sPoint).Select
    Sheet1.Select
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True

End Sub

I am using this code now. But this time the columns D and E in Sheet 2 remain empty. Yes the Values from K11-K16 in Sheet 1 are now being transferred to the right cells in Sheet 2. But values from Sheet 1 in Cells F2 & F4 are not being transferred.

I'm sorry if the mistake is done on my part. Im new to Excel and Macro.

Try this:

Sub Paste_Component()

    Application.ScreenUpdating = False

    Dim cName As String
    cName = Range("C4").Value
    Dim opRange As Range
    Dim opt1rng As Range
    Dim opt2rng As Range
    Set opRange = Range("K11:K16")
    Set opt1rng = Range("F2")
    Set opt2rng = Range("F4")

    Sheet2.Select
    Dim sPoint As String
    sPoint = Range("C5").Address(False, False)
    Range(sPoint).Select
    
    Do Until ActiveCell = vbNullString
        If ActiveCell = vbNullString Then
            ActiveCell.Select
        Else
            ActiveCell.Offset(1, 0).Select
            
        End If
    Loop
    
    ActiveCell = cName
    ActiveCell.Offset(0, 1) = opt1rng
    ActiveCell.Offset(0, 2) = opt2rng
    ActiveCell.Offset(0, 3).Select
    opRange.Copy
    Selection.PasteSpecial Paste:=xlValues, Transpose:=True
    Range(sPoint).Select
    Sheet1.Select
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True

End Sub

Did you copy the above code>Right Click the Save button, and replace the code with the copied code?

I get F2(M1) & F4(W1) in D5 & E5.

Yes I have done that. Ok now I have figured out the problem, It only works for the first time. Like when the Sheet 2 is clear/empty then F2 & F4 are transferred. It happens only once, but when you click the "Save Data" button again, then F2 & F4 are not transferred. But other values do.

Yes now its working. Thanks Alot