[Solved] VBA Code to Copy data from different worksheets

[Shane0278]

Hello,

I am seeking assistance to correct the VBA code in the attached worksheet. I am wanting to set it up so that when I press the button, the details from the form copy to the data worksheet

I am a VBA novice so not understanding why the code is not working.

Any help you can provide is appreciated

 

[shamimarita]

Hello Shane0278,

There are a few issues in your VBA code that might cause a runtime error:

Typo in Variable Name (rSH): The variable rSH is used in your code, but it is not declared or assigned anywhere. You likely meant to use Dsh (the data sheet) instead of rSH.
Correction: Replace all occurrences of rSH with Dsh in your code.

Incorrect Property (x1Up): The property x1Up is a typo. The correct property is xlUp.
Correction: Replace x1Up with xlUp.

Here is the updated code:

Sub transferData()

    Dim Fsh As Worksheet 'Form
    Dim Dsh As Worksheet 'Data

    'Set the worksheet
    Set Fsh = ThisWorkbook.Sheets("Form")
    Set Dsh = ThisWorkbook.Sheets("Data")

    'Get the next available row in the Data Sheet
    Dim nextRow As Integer
    nextRow = Dsh.Range("A" & Rows.Count).End(xlUp).Row + 1

    'Transfer data from the Form sheet to the Data sheet
    Dsh.Range("A" & nextRow).Value = Fsh.Range("B3").Value
    Dsh.Range("B" & nextRow).Value = Fsh.Range("F3").Value
    Dsh.Range("C" & nextRow).Value = Fsh.Range("B5").Value
    Dsh.Range("D" & nextRow).Value = Fsh.Range("F5").Value
    Dsh.Range("E" & nextRow).Value = Fsh.Range("B7").Value
    Dsh.Range("F" & nextRow).Value = Fsh.Range("F7").Value
    Dsh.Range("G" & nextRow).Value = Fsh.Range("F9").Value
    Dsh.Range("H" & nextRow).Value = Fsh.Range("B9").Value
    Dsh.Range("I" & nextRow).Value = Fsh.Range("A10").Value
    Dsh.Range("J" & nextRow).Value = Fsh.Range("A11").Value
    Dsh.Range("K" & nextRow).Value = Fsh.Range("A12").Value
    Dsh.Range("L" & nextRow).Value = Fsh.Range("A13").Value
    Dsh.Range("M" & nextRow).Value = Fsh.Range("A14").Value

    'Delete the form values
    On Error Resume Next 'Handle cases where the named range doesn't exist
    Fsh.Range("clearvalue").ClearContents
    On Error GoTo 0

    MsgBox "Saved", vbInformation, "Data Transfer"

End Sub

Here is the updated workbook. The button is working properly and saving form data in the Data sheet.

 

[Shane0278]

Good Afternoon,

Thank you for your assistance, it is greatly appreciated

 

[shamimarita]

You're very welcome! I'm glad I could assist. Let's keep helping each other and make the ExcelDemy forum a valuable resource for everyone. If you have more questions, feel free to ask. Have a great day!