Transfer Data from Different Cells and Copy to Other Sheet

M

manona

New Member
Joined
Mar 22, 2016
Messages
40
Hi! :)

I've built a basic form (not using the form function, just plain cells) in Excel, which users will input data. There are about 25 random individual cells for data entry, such as Name, School, Date, etc. In another sheet, I have built the frame for a small Database (e.g. A2 is "Name", B2 is "School", C2 is "Date", and so on).

I would like to add a button at the bottom of the form, so that once they have completed the data entry, data from the specific input cells are transferred to the Database under their respective areas.

A few requirements:
1) It needs to be a copy & paste VALUE.
2) The data will be kept there, so every time that button is pressed, the new data needs to be copied to the next available blank row.
3) FYI: I have a button that once they have completed the transfer, they click on it and it clears all of their input data.
4) If this is not too much to ask, is there a way to add a message box after the transfer is done, that says "Data was successfully transferred"?

Thanks a million in advance :)
 
Sort by date Sort by votes
Hi manona,

Not sure as it's working for me :confused:

Make sure the two worksheet variables correctly reflect the tabs with the raw data in it and the desired output tab on and that there's actually data in the cells you are linking from the raw data tab. You could also step through the code by opening the macro and pressing F8 to try and see what's happening.

If you can't resolve the issue you could try posting the workbook (devoid of all sensitive data) via a file sharing service like www.box.com and posting the link here.

Robert
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi Andy,

Thank you for your replies and for helping!

I used your code and made all necessary changes. When I tried running the macro, I got an error saying:

"Compilation Error - Sub or Function not defined" and takes me to the very first line of the code (which I changed for a different title - could this be why it's bugging?).

Here is my final code (it's French); any thoughts would be really appreciated.

Thanks in advance!

Manon



Sub BDELEVE()
'Those variables will be treated as string, including the date (this can be changed if you want)
ECOLE , ADRESSE, VILLE, TELEPHONE, NOM, PRENOM, AGE, NIVEAU, VDATE1, HEURE, LIEU, SURVEILLANCE, ACCIDENT, BLESSURE, Actions, COMMENTAIRES, TEMOIN1, VDATET1, TEMOIN2, VDATET2, RESPONSABLE, VDATER, Direction, VDATED, MARSH
'this variable is to find the last row with data in the DB sheet
Dim LastRow As Integer


'Here you retreive the data in the form (I don't know in what cells they are, but make the changes)
ECOLE = Sheets("F_Élève").Range("C11").Value
ADRESSE = Sheets("F_Élève").Range("C13").Value
VILLE = Sheets("F_Élève").Range("F11").Value
TELEPHONE = Sheets("F_Élève").Range("F13").Value
NOM = Sheets("F_Élève").Range("C17").Value
PRENOM = Sheets("F_Élève").Range("F17").Value
AGE = Sheets("F_Élève").Range("C19").Value
NIVEAU = Sheets("F_Élève").Range("F19").Value
VDATE1 = Sheets("F_Élève").Range("C23").Value
HEURE = Sheets("F_Élève").Range("F23").Value
LIEU = Sheets("F_Élève").Range("C25").Value
SURVEILLANCE = Sheets("F_Élève").Range("F25").Value
ACCIDENT = Sheets("F_Élève").Range("B29").Value
BLESSURE = Sheets("F_Élève").Range("B32").Value
ACTIONS = Sheets("F_Élève").Range("B35").Value
COMMENTAIRES = Sheets("F_").Range("B38").Value
TEMOIN1 = Sheets("F_Élève").Range("C42").Value
VDATET1 = Sheets("F_Élève").Range("C44").Value
TEMOIN2 = Sheets("F_Élève").Range("C46").Value
VDATET2 = Sheets("F_Élève").Range("C48").Value
RESPONSABLE = Sheets("F_Élève").Range("F42").Value
VDATER = Sheets("F_Élève").Range("F44").Value
Direction = Sheets("F_Élève").Range("F46").Value
VDATED = Sheets("F_Élève").Range("F48").Value
MARSH = Sheets("F_Élève").Range("F51").Value


Application.ScreenUpdating = False
'Assing the LastRow variable, but do a quick check for first time user
LastRow = Sheets("D_Base").Range("C" & Rows.Count).End(xlUp).Row + 1
If Sheets("D_Base ").Range("C10").Value = "" Then
Sheets("D_Base").Range("C10").Value = ECOLE
Sheets("D_Base").Range("D10").Value = ADRESSE
Sheets("D_Base").Range("E10").Value = VILLE
Sheets("D_Base").Range("F10").Value = TELEPHONE
Sheets("D_Base").Range("G10").Value = NOM
Sheets("D_Base").Range("H10").Value = PRENOM
Sheets("D_Base").Range("I10").Value = AGE
Sheets("D_Base").Range("J10").Value = NIVEAU
Sheets("D_Base").Range("K10").Value = VDATE1
Sheets("D_Base").Range("L10").Value = HEURE
Sheets("D_Base").Range("M10").Value = LIEU
Sheets("D_Base").Range("N10").Value = SURVEILLANCE
Sheets("D_Base").Range("O10").Value = ACCIDENT
Sheets("D_Base").Range("P10").Value = BLESSURE
Sheets("D_Base").Range("Q10").Value = ACTIONS
Sheets("D_Base").Range("R10").Value = COMMENTAIRES
Sheets("D_Base").Range("S10").Value = TEMOIN1
Sheets("D_Base").Range("T10").Value = VDATET1
Sheets("D_Base").Range("U10").Value = TEMOIN2
Sheets("D_Base").Range("V10").Value = VDATET2
Sheets("D_Base").Range("W10").Value = RESPONSABLE
Sheets("D_Base").Range("X10").Value = VDATER
Sheets("D_Base").Range("Y10").Value = Direction
Sheets("D_Base").Range("Z10").Value = VDATED
Sheets("D_Base").Range("AA10").Value = MARSH
Else
Sheets("D_Base").Range("C" & LastRow).Value = ECOLE
Sheets("D_Base").Range("D" & LastRow).Value = ADRESSE
Sheets("D_Base").Range("E" & LastRow).Value = VILLE
Sheets("D_Base").Range("F" & LastRow).Value = TELEPHONE
Sheets("D_Base").Range("G" & LastRow).Value = NOM
Sheets("D_Base").Range("H" & LastRow).Value = PRENOM
Sheets("D_Base").Range("I" & LastRow).Value = AGE
Sheets("D_Base").Range("J" & LastRow).Value = NIVEAU
Sheets("D_Base").Range("K" & LastRow).Value = VDATE1
Sheets("D_Base").Range("L" & LastRow).Value = HEURE
Sheets("D_Base").Range("M" & LastRow).Value = LIEU
Sheets("D_Base").Range("N" & LastRow).Value = SURVEILLANCE
Sheets("D_Base").Range("O" & LastRow).Value = ACCIDENT
Sheets("D_Base").Range("P" & LastRow).Value = BLESSURE
Sheets("D_Base").Range("Q" & LastRow).Value = ACTIONS
Sheets("D_Base").Range("R" & LastRow).Value = COMMENTAIRES
Sheets("D_Base").Range("S" & LastRow).Value = TEMOIN1
Sheets("D_Base").Range("T" & LastRow).Value = VDATET1
Sheets("D_Base").Range("U" & LastRow).Value = TEMOIN2
Sheets("D_Base").Range("V" & LastRow).Value = VDATET2
Sheets("D_Base").Range("W" & LastRow).Value = RESPONSABLE
Sheets("D_Base").Range("X" & LastRow).Value = VDATER
Sheets("D_Base").Range("Y" & LastRow).Value = Direction
Sheets("D_Base").Range("Z" & LastRow).Value = VDATED
Sheets("D_Base").Range("AA" & LastRow).Value = MARSH
End If


'Your message
MsgBox "Les données ont bien été enregistrées."


'you can clear the form right here so no need to click two different buttons
'but just ignore and delete the following code if you want.
Sheets("F_Élève").Range("C11, C13, F11, F13, C17, F17, C19, F19, C23, F23, C25, F25, B29, B32, B35, B38, C42, C44, C46, C48, F42, F44, F46, F48, F51").ClearContents 'add all cells or ranges you want


Application.ScreenUpdating = True
End Sub
 
Upvote 0
You are missing the "Dim" word to declare the variables
Dim ECOLE , ADRESSE, VILLE, TELEPHONE, NOM, PRENOM, AGE, NIVEAU, VDATE1, HEURE, LIEU, SURVEILLANCE, ACCIDENT, BLESSURE, Actions, COMMENTAIRES, TEMOIN1, VDATET1, TEMOIN2, VDATET2, RESPONSABLE, VDATER, Direction, VDATED, MARSH
 
Upvote 0
Hi manona,
I have tried your workbook, I place the code in a standar module. Becareful with the sheets names, I have had to edit lot of them due to subscription out of range error, so make sure all sheet names are correct in the code. Also you can not leave a space between cells in the last line where you clear the form. The code is working fine! :D

should like this:
Sheets("F_Élève").Range("C11,C13,F11,F13,C17,F17,C19,F19,C23,F23,C25,F25,B29,B32,B35,B38,C42,C44,C46,C48,F42,F44,F46,F48,F51").ClearContents

NOT like this:
Sheets("F_ƒl�ve").Range("C11, C13, F11, F13, C17, F17, C19, F19, C23, F23, C25, F25, B29, B32, B35, B38, C42, C44, C46, C48, F42, F44, F46, F48, F51").ClearContents

Your workbook looks awesome! Good job
 
Upvote 0
Hello again,
Here is a final code which is working for me in your shared workbook. Note that I have added a new line at the top, I think it is important to have at least one required field.
Also, I recomend you to place the code in a standar module, not the Workbook module. I have improved the code by using a With/End with for the DB sheet

Code: 
Sub BDELEVE()
'This is a new line. At least one field should be required, I picked up C11
'you can chose another cell but that cell's info will be the one to find the last row in DB
'so, make it match with this line: LastRow = Sheets("D_Base").Range("C" & Rows.Count).End(xlUp).Row + 1
If Sheets("F_Élève").Range("C11").Value = "" Then MsgBox "ECOLE is required, please enter some data in C11", vbExclamation, "Missing Info...": Exit Sub
'you can add more required fields here
''''''''''''''''''''''''''''''''''''''


'Those variables will be treated as string, including the date (this can be changed if you want)
Dim ECOLE, ADRESSE, VILLE, TELEPHONE, NOM, PRENOM, AGE, NIVEAU, VDATE1, HEURE, LIEU, SURVEILLANCE, ACCIDENT, BLESSURE, Actions, COMMENTAIRES, TEMOIN1, VDATET1, TEMOIN2, VDATET2, RESPONSABLE, VDATER, Direction, VDATED, MARSH
'this variable is to find the last row with data in the DB sheet
Dim LastRow As Integer


'Here you retreive the data in the form (I don't know in what cells they are, but make the changes)
ECOLE = Sheets("F_Élève").Range("C11").Value
ADRESSE = Sheets("F_Élève").Range("C13").Value
VILLE = Sheets("F_Élève").Range("F11").Value
TELEPHONE = Sheets("F_Élève").Range("F13").Value
NOM = Sheets("F_Élève").Range("C17").Value
PRENOM = Sheets("F_Élève").Range("F17").Value
AGE = Sheets("F_Élève").Range("C19").Value
NIVEAU = Sheets("F_Élève").Range("F19").Value
VDATE1 = Sheets("F_Élève").Range("C23").Value
HEURE = Sheets("F_Élève").Range("F23").Value
LIEU = Sheets("F_Élève").Range("C25").Value
SURVEILLANCE = Sheets("F_Élève").Range("F25").Value
ACCIDENT = Sheets("F_Élève").Range("B29").Value
BLESSURE = Sheets("F_Élève").Range("B32").Value
Actions = Sheets("F_Élève").Range("B35").Value
COMMENTAIRES = Sheets("F_Élève").Range("B38").Value
TEMOIN1 = Sheets("F_Élève").Range("C42").Value
VDATET1 = Sheets("F_Élève").Range("C44").Value
TEMOIN2 = Sheets("F_Élève").Range("C46").Value
VDATET2 = Sheets("F_Élève").Range("C48").Value
RESPONSABLE = Sheets("F_Élève").Range("F42").Value
VDATER = Sheets("F_Élève").Range("F44").Value
Direction = Sheets("F_Élève").Range("F46").Value
VDATED = Sheets("F_Élève").Range("F48").Value
MARSH = Sheets("F_Élève").Range("F51").Value


Application.ScreenUpdating = False
'Assing the LastRow variable, but do a quick check for first time user
LastRow = Sheets("D_Base").Range("C" & Rows.Count).End(xlUp).Row + 1
If Sheets("D_Base").Range("C10").Value = "" Then
With Sheets("D_Base")
    .Range("C10").Value = ECOLE
    .Range("D10").Value = ADRESSE
    .Range("E10").Value = VILLE
    .Range("F10").Value = TELEPHONE
    .Range("G10").Value = NOM
    .Range("H10").Value = PRENOM
    .Range("I10").Value = AGE
    .Range("J10").Value = NIVEAU
    .Range("K10").Value = VDATE1
    .Range("L10").Value = HEURE
    .Range("M10").Value = LIEU
    .Range("N10").Value = SURVEILLANCE
    .Range("O10").Value = ACCIDENT
    .Range("P10").Value = BLESSURE
    .Range("Q10").Value = Actions
    .Range("R10").Value = COMMENTAIRES
    .Range("S10").Value = TEMOIN1
    .Range("T10").Value = VDATET1
    .Range("U10").Value = TEMOIN2
    .Range("V10").Value = VDATET2
    .Range("W10").Value = RESPONSABLE
    .Range("X10").Value = VDATER
    .Range("Y10").Value = Direction
    .Range("Z10").Value = VDATED
    .Range("AA10").Value = MARSH
End With
Else
With Sheets("D_Base")
    .Range("C" & LastRow).Value = ECOLE
    .Range("D" & LastRow).Value = ADRESSE
    .Range("E" & LastRow).Value = VILLE
    .Range("F" & LastRow).Value = TELEPHONE
    .Range("G" & LastRow).Value = NOM
    .Range("H" & LastRow).Value = PRENOM
    .Range("I" & LastRow).Value = AGE
    .Range("J" & LastRow).Value = NIVEAU
    .Range("K" & LastRow).Value = VDATE1
    .Range("L" & LastRow).Value = HEURE
    .Range("M" & LastRow).Value = LIEU
    .Range("N" & LastRow).Value = SURVEILLANCE
    .Range("O" & LastRow).Value = ACCIDENT
    .Range("P" & LastRow).Value = BLESSURE
    .Range("Q" & LastRow).Value = Actions
    .Range("R" & LastRow).Value = COMMENTAIRES
    .Range("S" & LastRow).Value = TEMOIN1
    .Range("T" & LastRow).Value = VDATET1
    .Range("U" & LastRow).Value = TEMOIN2
    .Range("V" & LastRow).Value = VDATET2
    .Range("W" & LastRow).Value = RESPONSABLE
    .Range("X" & LastRow).Value = VDATER
    .Range("Y" & LastRow).Value = Direction
    .Range("Z" & LastRow).Value = VDATED
    .Range("AA" & LastRow).Value = MARSH
End With
End If


'Your message
MsgBox "Les donnŽes ont bien ŽtŽ enregistrŽes.", vbInformation, "Continuer..."


'you can clear the form right here so no need to click two different buttons
'but just ignore and delete the following code if you want.
Sheets("F_Élève").Range("C11,C13,F11,F13,C17,F17,C19,F19,C23,F23,C25,F25,B29,B32,B35,B38,C42,C44,C46,C48,F42,F44,F46,F48,F51").ClearContents 'add all cells or ranges you want


Application.ScreenUpdating = True
End sub
 
Last edited:
Upvote 0
Hi Andy,

THANK YOU so much, the code worked perfectly! :D It's exactly what I needed. Also thanks for the tips!

I really appreciate your help. :)

Manon
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
P
  • Solved
Transferring data between two userforms problem .
Replies
7
Views
513
Pacman52
P
W
  • Solved
Moving/transferring data between tables with matching column names, not by column order - vba
Replies
5
Views
916
Alex Blakenburg
A
U
  • Question Question
Information overwriting when transferring
Replies
7
Views
485
UTB
U
I
  • Solved
Subscript out of range when copying values to another sheet
Replies
9
Views
187
ipbr21054
I

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top