VBA to retrieve data

K

K_Stevs1

New Member
Joined
Jan 27, 2022
Messages
27
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi,

Is there vba to retrieve data from a specific row in Sheet2 (Based on cell f5) and back into the correct Cells in Sheet1 to allow a form to be completed by another person or updated, then resubmitted and overwrite the data that was there originally?

Thanks
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Sure there is. If you can provide more details about your data and how you want it to work, then I'm sure someone can provide you with something.

How do we know which "specific row" to look for, and how does F5 affect it? What column(s) is the data in on sheet2?

What are the "correct cells" on Sheet1?

What triggers the code to look on Sheet2 for data? (User enters data on sheet1 in specific cell, user clicks a button, etc)

What data is being overwritten?
 
Upvote 0
Hi Thank you,

Theses are the copies of the form I am looking at, the user will complete the form and another user can then go back into the form, enter ID number and retrieve all the data back into the boxes, edit and resubmit, over writing the original data.

Admission Section
Date
ID1
3+
4hours
5
6
7Yes
8
9
1011Unknown
12Yes13
Section b
1415
1617
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
section c
353637
38
39
40
41
42
43
44
45
46days
47
48dayshours
49
50
51Yes



DateID345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
23/05/20241391137NoYesYesYesYesYesYes23.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo2NoYesYes
24/05/20242391137NoYesYesYesYesYesYes24.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo3NoYesYes
25/05/20243391137NoYesYesYesYesYesYes25.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo4NoYesYes
26/05/20244391137NoYesYesYesYesYesYes26.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo5NoYesYes
27/05/20245391137NoYesYesYesYesYesYes27.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo6NoYesYes
28/05/20246391137NoYesYesYesYesYesYes28.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo7NoYesYes
29/05/20247391137NoYesYesYesYesYesYes29.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo8NoYesYes
30/05/20248391137NoYesYesYesYesYesYes30.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo9NoYesYes
31/05/20249391137NoYesYesYesYesYesYes31.3NoNoNoneFree textPositiveNoNoNoNoNoSVBCLURespiratory Distress / GruntingOwn910NoNoNoYesNoNoNoNo10NoYesYes


Thank for any help given
 
Upvote 0
How does another user resubmit the form?

Also, please see the two red fields below and answer, where do those values come from?
Book1 7-29-2024.xlsm
ABCDEFGHIJKLMN
1Admission Section
2
3
4Date
5ID
63+
74hours
85
96
107
11
128
139
141011
151213
16
17Section b
181415
191617
20
2118
2219
2320
2421
2522
26
27
2823
29
3024
3125
32
3326
3427
35
3628
3729
38
3930
40
41
4231
4332
4433
4534
46
47section c
48353637
49
50
5138
5239
5340
54
55
5641
5742
5843
5944
6045
61
62
6346days
6447
6548dayshours
6649
67
68
6950
70
7151
72
Sheet1
 
Upvote 0
Hi,

The 1st one is column E and 2nd one is column AW on the Data worksheet.

Thanks
dreid1011 said:
How does another user resubmit the form?

Also, please see the two red fields below and answer, where do those values come from?
Book1 7-29-2024.xlsm
ABCDEFGHIJKLMN
1Admission Section
2
3
4Date
5ID
63+
74hours
85
96
107
11
128
139
141011
151213
16
17Section b
181415
191617
20
2118
2219
2320
2421
2522
26
27
2823
29
3024
3125
32
3326
3427
35
3628
3729
38
3930
40
41
4231
4332
4433
4534
46
47section c
48353637
49
50
5138
5239
5340
54
55
5641
5742
5843
5944
6045
61
62
6346days
6447
6548dayshours
6649
67
68
6950
70
7151
72
Sheet1
Click to expand...
 
Upvote 0
And how does the form get resubmitted? Is there a button to be pressed or some other event?
 
Upvote 0
dreid1011 said:
Okay, perfect.

Now, a little clarification.

Column E is already filling #5 and AW is filling #49?
Click to expand...
Hi,

This is the base code but doesn't work at present and keeps having errors.

VBA Code: 
Sub Button2_Click()
Dim Nex As Integer
If Form.Cells(F5).Value <> "" And Form.Cells(F6).Value <> "" Then
    Form.Unprotect
    Data.Unprotect
    Application.ScreenUpdating = False
    Nex = Data.Range("A1048576").End(xlUp).Row + 1
    Data.Cells(Nex, 1).Value = Form.Cells(5, 6).Value
    Data.Cells(Nex, 2).Value = Form.Cells(6, 6).Value
    Data.Cells(Nex, 3).Value = Form.Cells(7, 6).Value
    Data.Cells(Nex, 4).Value = Form.Cells(7, 9).Value
    Data.Cells(Nex, 5).Value = Form.Cells(8, 6).Value
    Data.Cells(Nex, 6).Value = Form.Cells(9, 6).Value
    Data.Cells(Nex, 7).Value = Form.Cells(10, 12).Value
    Data.Cells(Nex, 8).Value = Form.Cells(11, 12).Value
    Data.Cells(Nex, 9).Value = Form.Cells(13, 12).Value
    Data.Cells(Nex, 10).Value = Form.Cells(15, 5).Value
    Data.Cells(Nex, 11).Value = Form.Cells(16, 5).Value
    Data.Cells(Nex, 12).Value = Form.Cells(15, 12).Value
    Data.Cells(Nex, 13).Value = Form.Cells(16, 12).Value
    
    Data.Cells(Nex, 14).Value = Form.Cells(19, 5).Value
    Data.Cells(Nex, 15).Value = Form.Cells(20, 5).Value
    Data.Cells(Nex, 16).Value = Form.Cells(19, 12).Value
    Data.Cells(Nex, 17).Value = Form.Cells(20, 12).Value
    Data.Cells(Nex, 18).Value = Form.Cells(22, 6).Value
    Data.Cells(Nex, 19).Value = Form.Cells(23, 12).Value
    Data.Cells(Nex, 20).Value = Form.Cells(24, 12).Value
    Data.Cells(Nex, 21).Value = Form.Cells(25, 6).Value
    Data.Cells(Nex, 22).Value = Form.Cells(26, 6).Value
    Data.Cells(Nex, 23).Value = Form.Cells(29, 6).Value
    Data.Cells(Nex, 24).Value = Form.Cells(31, 12).Value
    Data.Cells(Nex, 25).Value = Form.Cells(32, 6).Value
    Data.Cells(Nex, 26).Value = Form.Cells(34, 12).Value
    
    Data.Cells(Nex, 27).Value = Form.Cells(35, 6).Value
    Data.Cells(Nex, 28).Value = Form.Cells(37, 6).Value
    Data.Cells(Nex, 29).Value = Form.Cells(38, 12).Value
    Data.Cells(Nex, 30).Value = Form.Cells(40, 12).Value
    Data.Cells(Nex, 31).Value = Form.Cells(43, 6).Value
    Data.Cells(Nex, 32).Value = Form.Cells(44, 6).Value
    Data.Cells(Nex, 33).Value = Form.Cells(45, 6).Value
    Data.Cells(Nex, 34).Value = Form.Cells(46, 6).Value
    Data.Cells(Nex, 35).Value = Form.Cells(49, 4).Value
    Data.Cells(Nex, 36).Value = Form.Cells(49, 9).Value
    Data.Cells(Nex, 37).Value = Form.Cells(49, 14).Value
    Data.Cells(Nex, 38).Value = Form.Cells(52, 12).Value
    Data.Cells(Nex, 39).Value = Form.Cells(53, 12).Value
    Data.Cells(Nex, 40).Value = Form.Cells(54, 12).Value
    
    Data.Cells(Nex, 41).Value = Form.Cells(57, 12).Value
    Data.Cells(Nex, 42).Value = Form.Cells(58, 12).Value
    Data.Cells(Nex, 43).Value = Form.Cells(59, 12).Value
    Data.Cells(Nex, 44).Value = Form.Cells(60, 12).Value
    Data.Cells(Nex, 45).Value = Form.Cells(61, 12).Value
    Data.Cells(Nex, 46).Value = Form.Cells(64, 6).Value
    Data.Cells(Nex, 47).Value = Form.Cells(65, 12).Value
    Data.Cells(Nex, 48).Value = Form.Cells(66, 6).Value
    Data.Cells(Nex, 49).Value = Form.Cells(66, 10).Value
    Data.Cells(Nex, 50).Value = Form.Cells(67, 6).Value
    Data.Cells(Nex, 51).Value = Form.Cells(70, 12).Value
    Data.Cells(Nex, 52).Value = Form.Cells(72, 12).Value
    
    Data.Cells(Nex, 53).Value = Form.Cells(82, 12).Value
    Data.Cells(Nex, 54).Value = Form.Cells(84, 12).Value
    Data.Cells(Nex, 55).Value = Form.Cells(85, 12).Value
    Data.Cells(Nex, 56).Value = Form.Cells(86, 12).Value
    Data.Cells(Nex, 57).Value = Form.Cells(88, 12).Value
    Data.Cells(Nex, 58).Value = Form.Cells(89, 12).Value
    Data.Cells(Nex, 59).Value = Form.Cells(92, 12).Value
    Data.Cells(Nex, 60).Value = Form.Cells(93, 12).Value
    Data.Cells(Nex, 61).Value = Form.Cells(96, 12).Value
    Data.Cells(Nex, 62).Value = Form.Cells(98, 12).Value
    
    Data.Cells(Nex, 63).Value = Form.Cells(109, 12).Value
    Data.Cells(Nex, 64).Value = Form.Cells(111, 12).Value
    Data.Cells(Nex, 65).Value = Form.Cells(112, 12).Value
    Data.Cells(Nex, 66).Value = Form.Cells(114, 12).Value
    Data.Cells(Nex, 67).Value = Form.Cells(116, 12).Value
    Data.Cells(Nex, 68).Value = Form.Cells(117, 12).Value
    Data.Cells(Nex, 69).Value = Form.Cells(118, 12).Value

     Form.Cells(5, 6).Value = ""
     Form.Cells(6, 6).Value = ""
     Form.Cells(7, 6).Value = ""
     Form.Cells(7, 9).Value = ""
     Form.Cells(8, 6).Value = ""
     Form.Cells(9, 6).Value = ""
     Form.Cells(10, 12).Value = ""
     Form.Cells(11, 12).Value = ""
     Form.Cells(13, 12).Value = ""
     Form.Cells(15, 5).Value = ""
     Form.Cells(16, 5).Value = ""
     Form.Cells(15, 12).Value = ""
     Form.Cells(16, 12).Value = ""
    
     Form.Cells(19, 5).Value = ""
     Form.Cells(20, 5).Value = ""
     Form.Cells(19, 12).Value = ""
     Form.Cells(20, 12).Value = ""
     Form.Cells(22, 6).Value = ""
     Form.Cells(23, 12).Value = ""
     Form.Cells(24, 12).Value = ""
     Form.Cells(25, 6).Value = ""
     Form.Cells(26, 6).Value = ""
     Form.Cells(29, 6).Value = ""
     Form.Cells(31, 12).Value = ""
     Form.Cells(32, 6).Value = ""
     Form.Cells(34, 12).Value = ""
    
     Form.Cells(35, 6).Value = ""
     Form.Cells(37, 6).Value = ""
     Form.Cells(38, 12).Value = ""
     Form.Cells(40, 12).Value = ""
     Form.Cells(43, 6).Value = ""
     Form.Cells(44, 6).Value = ""
     Form.Cells(45, 6).Value = ""
     Form.Cells(46, 6).Value = ""
     Form.Cells(49, 4).Value = ""
     Form.Cells(49, 9).Value = ""
     Form.Cells(49, 14).Value = ""
     Form.Cells(52, 12).Value = ""
     Form.Cells(53, 12).Value = ""
     Form.Cells(54, 12).Value = ""
    
     Form.Cells(57, 12).Value = ""
     Form.Cells(58, 12).Value = ""
     Form.Cells(59, 12).Value = ""
     Form.Cells(60, 12).Value = ""
     Form.Cells(61, 12).Value = ""
     Form.Cells(64, 6).Value = ""
     Form.Cells(65, 12).Value = ""
     Form.Cells(66, 6).Value = ""
     Form.Cells(66, 10).Value = ""
     Form.Cells(67, 6).Value = ""
     Form.Cells(70, 12).Value = ""
     Form.Cells(72, 12).Value = ""
    
     Form.Cells(82, 12).Value = ""
     Form.Cells(84, 12).Value = ""
     Form.Cells(85, 12).Value = ""
     Form.Cells(86, 12).Value = ""
     Form.Cells(88, 12).Value = ""
     Form.Cells(89, 12).Value = ""
     Form.Cells(92, 12).Value = ""
     Form.Cells(93, 12).Value = ""
     Form.Cells(96, 12).Value = ""
     Form.Cells(98, 12).Value = ""
    
     Form.Cells(109, 12).Value = ""
     Form.Cells(111, 12).Value = ""
     Form.Cells(112, 12).Value = ""
     Form.Cells(114, 12).Value = ""
     Form.Cells(116, 12).Value = ""
     Form.Cells(117, 12).Value = ""
     Form.Cells(118, 12).Value = ""
     Data.Protect
     Form.Protect
    Data.Activate
    Application.ScreenUpdating = True
    MsgBox "Your data has successfully been submitted!", vbOKOnly, "Success!"
Else
    MsgBox "Please ensure that the Date of MDT Review & the local identifier has been completed before trying to submit the data.", vbOKOnly + vbCritical, "Error: Required Fields Missing"
    Exit Sub
End If
End Sub

The information entered into the light cyan cells where possible, then submitted by the person clicking on the submit button. this is then copy and pasted into the correct rows (This is what I would like it to do) on the data tab.

Then another person can later enter the ID number and retrieve the data from 'Data tab' update the information and click the button, overwriting the information for that specific ID number in the 'Data tab'.

Thanks
 
Upvote 0
Alright, see if this does what you want. There are three subs here.

The first will pull the record info from 'Data' when an ID is entered into F5 (E5 because it's a merged cell). If the ID cannot be found on 'Data' then it will prompt the user asking if it is a new ID or not. If not, then it clears the form. If it is a new ID, then it does not clear the form, and allows for data entry.

The second will add/update record info from 'Form' to 'Data' when the form is submitted. *If you are using an ActiveX Form Control button, then you must rename the button to match the sub name, or rename the sub name to match the button name. (It looks like you are using this type of button.)

The third is the clear form code but in it's own sub to keep the clutter down since it is used a couple times.

In each sub, I commented out the lines for fields 52 to 69 because your sample data did not include them, but your code does. So if you need them, just uncomment them. Also, your code currently does not include the two fields I highlighted in red above, so I still do not know where that data is supposed to come from.

Also, I imagine the errors you are encountering, are partly due to your cells being merged and you are not referencing the correct cells. And you are directly referencing sheet names as if they were variables.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim newID
Dim idRow As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Form")
Set ws2 = Sheets("Data")

Application.EnableEvents = False
Application.ScreenUpdating = False

On Error GoTo ExitNow

If Not Intersect(Target, Range("E5:H5")) Is Nothing Then
    ws1.Unprotect
    ws2.Unprotect
    idRow = 0
    For i = 2 To ws2.Range("B" & Rows.Count).End(xlUp).Row
        If ws2.Range("B" & i) = Target Then
            idRow = i
            Exit For
        End If
    Next i
    If idRow > 0 Then
        ws1.Range("E4") = ws2.Range("A" & idRow) 'Date
        ws1.Range("E6") = ws2.Range("C" & idRow) '3
        'ws1.Range("H6") = ?? 'What about H6?
        ws1.Range("E7") = ws2.Range("D" & idRow) '4
        ws1.Range("E8") = ws2.Range("E" & idRow) '5
        ws1.Range("K9") = ws2.Range("F" & idRow) '6
        ws1.Range("K10") = ws2.Range("G" & idRow) '7
        ws1.Range("K12") = ws2.Range("H" & idRow) '8
        ws1.Range("D14") = ws2.Range("J" & idRow) '10
        ws1.Range("K14") = ws2.Range("K" & idRow) '11
        ws1.Range("D15") = ws2.Range("L" & idRow) '12
        ws1.Range("K15") = ws2.Range("M" & idRow) '13
        'Section b
        ws1.Range("D18") = ws2.Range("N" & idRow) '14
        ws1.Range("K18") = ws2.Range("O" & idRow) '15
        ws1.Range("D19") = ws2.Range("P" & idRow) '16
        ws1.Range("K19") = ws2.Range("Q" & idRow) '17
        ws1.Range("E21") = ws2.Range("R" & idRow) '18
        ws1.Range("K22") = ws2.Range("S" & idRow) '19
        ws1.Range("K23") = ws2.Range("T" & idRow) '20
        ws1.Range("E24") = ws2.Range("U" & idRow) '21
        ws1.Range("E25") = ws2.Range("V" & idRow) '22
        ws1.Range("E28") = ws2.Range("W" & idRow) '23
        ws1.Range("K30") = ws2.Range("X" & idRow) '24
        ws1.Range("E31") = ws2.Range("Y" & idRow) '25
        ws1.Range("K33") = ws2.Range("Z" & idRow) '26
        ws1.Range("E34") = ws2.Range("AA" & idRow) '27
        ws1.Range("E36") = ws2.Range("AB" & idRow) '28
        ws1.Range("K37") = ws2.Range("AC" & idRow) '29
        ws1.Range("K39") = ws2.Range("AD" & idRow) '30
        ws1.Range("E42") = ws2.Range("AE" & idRow) '31
        ws1.Range("E43") = ws2.Range("AF" & idRow) '32
        ws1.Range("E44") = ws2.Range("AG" & idRow) '33
        ws1.Range("E45") = ws2.Range("AH" & idRow) '34
        'Section c
        ws1.Range("C48") = ws2.Range("AI" & idRow) '35
        ws1.Range("H48") = ws2.Range("AJ" & idRow) '36
        ws1.Range("M48") = ws2.Range("AK" & idRow) '37
        ws1.Range("K51") = ws2.Range("AL" & idRow) '38
        ws1.Range("K52") = ws2.Range("AM" & idRow) '39
        ws1.Range("K53") = ws2.Range("AN" & idRow) '40
        ws1.Range("K56") = ws2.Range("AO" & idRow) '41
        ws1.Range("K57") = ws2.Range("AP" & idRow) '42
        ws1.Range("K58") = ws2.Range("AQ" & idRow) '43
        ws1.Range("K59") = ws2.Range("AR" & idRow) '44
        ws1.Range("K60") = ws2.Range("AS" & idRow) '45
        ws1.Range("E63") = ws2.Range("AT" & idRow) '46
        ws1.Range("K64") = ws2.Range("AU" & idRow) '47
        ws1.Range("E65") = ws2.Range("AV" & idRow) '48
        'ws1.Range("I65") = ?? 'What about I65?
        ws1.Range("E66") = ws2.Range("AW" & idRow) '49
        ws1.Range("K69") = ws2.Range("AX" & idRow) '50
        ws1.Range("K71") = ws2.Range("AY" & idRow) '51
        
        'Sample data did not include these fields
'        ws1.Range("K73") = ws2.Range("AZ" & idRow) '52
'        ws1.Range("K82") = ws2.Range("BA" & idRow) '53
'        ws1.Range("K84") = ws2.Range("BB" & idRow) '54
'        ws1.Range("K85") = ws2.Range("BC" & idRow) '55
'        ws1.Range("K86") = ws2.Range("BD" & idRow) '56
'        ws1.Range("K88") = ws2.Range("BE" & idRow) '57
'        ws1.Range("K89") = ws2.Range("BF" & idRow) '58
'        ws1.Range("K92") = ws2.Range("BG" & idRow) '59
'        ws1.Range("K93") = ws2.Range("BH" & idRow) '60
'        ws1.Range("K96") = ws2.Range("BI" & idRow) '61
'        ws1.Range("K98") = ws2.Range("BJ" & idRow) '62
'        ws1.Range("K109") = ws2.Range("BK" & idRow) '63
'        ws1.Range("K111") = ws2.Range("BL" & idRow) '64
'        ws1.Range("K112") = ws2.Range("BM" & idRow) '65
'        ws1.Range("K114") = ws2.Range("BN" & idRow) '66
'        ws1.Range("K116") = ws2.Range("BO" & idRow) '67
'        ws1.Range("K117") = ws2.Range("BP" & idRow) '68
'        ws1.Range("K118") = ws2.Range("BQ" & idRow) '69

        
    Else
        'Clear form if ID not found on Data sheet and not entering a new record
        newID = MsgBox("ID not found. Enter new record?", vbYesNo, "Unknown ID")
        If newID = vbNo Then
            Call FormClear
            GoTo ExitNow
        End If
    End If
End If

ExitNow:
ws1.Protect
ws2.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

VBA Code: 
Private Sub SubmitButton_Click()
Dim idRow As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Form")
Set ws2 = Sheets("Data")

ws1.Unprotect
ws2.Unprotect

idRow = 0

For i = 2 To ws2.Range("B" & Rows.Count).End(xlUp).Row
    If ws2.Range("B" & i) = ws1.Range("E5") Then
        idRow = i
        Exit For
    End If
Next i

If idRow = 0 Then
    idRow = ws2.Range("B" & Rows.Count).End(xlUp).Row + 1
End If

If ws1.Range("E5") <> "" And ws1.Range("E6") <> "" Then
    ws2.Range("A" & idRow) = ws1.Range("E4") 'Date
    ws2.Range("B" & idRow) = ws1.Range("E5") 'ID
    ws2.Range("C" & idRow) = ws1.Range("E6") '3
    'What about H6? ws1.Range("H6") = ??
    ws2.Range("D" & idRow) = ws1.Range("E7") '4
    ws2.Range("E" & idRow) = ws1.Range("E8") '5
    ws2.Range("F" & idRow) = ws1.Range("K9") '6
    ws2.Range("G" & idRow) = ws1.Range("K10") '7
    ws2.Range("H" & idRow) = ws1.Range("K12") '8
    ws2.Range("J" & idRow) = ws1.Range("D14") '10
    ws2.Range("K" & idRow) = ws1.Range("K14") '11
    ws2.Range("L" & idRow) = ws1.Range("D15") '12
    ws2.Range("M" & idRow) = ws1.Range("K15") '13
    'Section b
    ws2.Range("N" & idRow) = ws1.Range("D18") '14
    ws2.Range("O" & idRow) = ws1.Range("K18") '15
    ws2.Range("P" & idRow) = ws1.Range("D19") '16
    ws2.Range("Q" & idRow) = ws1.Range("K19") '17
    ws2.Range("R" & idRow) = ws1.Range("E21") '18
    ws2.Range("S" & idRow) = ws1.Range("K22") '19
    ws2.Range("T" & idRow) = ws1.Range("K23") '20
    ws2.Range("U" & idRow) = ws1.Range("E24") '21
    ws2.Range("V" & idRow) = ws1.Range("E25") '22
    ws2.Range("W" & idRow) = ws1.Range("E28") '23
    ws2.Range("X" & idRow) = ws1.Range("K30") '24
    ws2.Range("Y" & idRow) = ws1.Range("E31") '25
    ws2.Range("Z" & idRow) = ws1.Range("K33") '26
    ws2.Range("AA" & idRow) = ws1.Range("E34") '27
    ws2.Range("AB" & idRow) = ws1.Range("E36") '28
    ws2.Range("AC" & idRow) = ws1.Range("K37") '29
    ws2.Range("AD" & idRow) = ws1.Range("K39") '30
    ws2.Range("AE" & idRow) = ws1.Range("E42") '31
    ws2.Range("AF" & idRow) = ws1.Range("E43") '32
    ws2.Range("AG" & idRow) = ws1.Range("E44") '33
    ws2.Range("AH" & idRow) = ws1.Range("E45") '34
    'Section c
    ws2.Range("AI" & idRow) = ws1.Range("C48") '35
    ws2.Range("AJ" & idRow) = ws1.Range("H48") '36
    ws2.Range("AK" & idRow) = ws1.Range("M48") '37
    ws2.Range("AL" & idRow) = ws1.Range("K51") '38
    ws2.Range("AM" & idRow) = ws1.Range("K52") '39
    ws2.Range("AN" & idRow) = ws1.Range("K53") '40
    ws2.Range("AO" & idRow) = ws1.Range("K56") '41
    ws2.Range("AP" & idRow) = ws1.Range("K57") '42
    ws2.Range("AQ" & idRow) = ws1.Range("K58") '43
    ws2.Range("AR" & idRow) = ws1.Range("K59") '44
    ws2.Range("AS" & idRow) = ws1.Range("K60") '45
    ws2.Range("AT" & idRow) = ws1.Range("E63") '46
    ws2.Range("AU" & idRow) = ws1.Range("K64") '47
    ws2.Range("AV" & idRow) = ws1.Range("E65") '48
    'What about I65? ws1.Range("I65") = ??
    ws2.Range("AW" & idRow) = ws1.Range("E66") '49
    ws2.Range("AX" & idRow) = ws1.Range("K69") '50
    ws2.Range("AY" & idRow) = ws1.Range("K71") '51
    
    'Sample data did not include these fields
'    ws2.Range("AZ" & idRow) = ws1.Range("K73") '52
'    ws2.Range("BA" & idRow) = ws1.Range("K82") '53
'    ws2.Range("BB" & idRow) = ws1.Range("K84") '54
'    ws2.Range("BC" & idRow) = ws1.Range("K85") '55
'    ws2.Range("BD" & idRow) = ws1.Range("K86") '56
'    ws2.Range("BE" & idRow) = ws1.Range("K88") '57
'    ws2.Range("BF" & idRow) = ws1.Range("K89") '58
'    ws2.Range("BG" & idRow) = ws1.Range("K92") '59
'    ws2.Range("BH" & idRow) = ws1.Range("K93") '60
'    ws2.Range("BI" & idRow) = ws1.Range("K96") '61
'    ws2.Range("BJ" & idRow) = ws1.Range("K98") '62
'    ws2.Range("BK" & idRow) = ws1.Range("K109") '63
'    ws2.Range("BL" & idRow) = ws1.Range("K111") '64
'    ws2.Range("BM" & idRow) = ws1.Range("K112") '65
'    ws2.Range("BN" & idRow) = ws1.Range("K114") '66
'    ws2.Range("BO" & idRow) = ws1.Range("K116") '67
'    ws2.Range("BP" & idRow) = ws1.Range("K117") '68
'    ws2.Range("BQ" & idRow) = ws1.Range("K118") '69
    
    
    Call FormClear
    ws2.Activate
    MsgBox "Your data has successfully been submitted!", vbOKOnly, "Success!"
Else
    MsgBox "Please ensure that the Date of MDT Review & the local identifier has been completed before trying to submit the data.", vbOKOnly + vbCritical, "Error: Required Fields Missing"
End If

ws1.Protect
ws2.Protect

End Sub

VBA Code: 
Private Sub FormClear()
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Form")
Set ws2 = Sheets("Data")

ws1.Range("E4").ClearContents 'Date
ws1.Range("E5").ClearContents 'ID
ws1.Range("E6").ClearContents '3
'ws1.Range("H6") = ?? 'What about H6?
ws1.Range("E7").ClearContents '4
ws1.Range("E8").ClearContents '5
ws1.Range("K9").ClearContents '6
ws1.Range("K10").ClearContents '7
ws1.Range("K12").ClearContents '8
ws1.Range("D14").ClearContents '10
ws1.Range("K14").ClearContents '11
ws1.Range("D15").ClearContents '12
ws1.Range("K15").ClearContents '13
'Section b
ws1.Range("D18").ClearContents '14
ws1.Range("K18").ClearContents '15
ws1.Range("D19").ClearContents '16
ws1.Range("K19").ClearContents '17
ws1.Range("E21").ClearContents '18
ws1.Range("K22").ClearContents '19
ws1.Range("K23").ClearContents '20
ws1.Range("E24").ClearContents '21
ws1.Range("E25").ClearContents '22
ws1.Range("E28").ClearContents '23
ws1.Range("K30").ClearContents '24
ws1.Range("E31").ClearContents '25
ws1.Range("K33").ClearContents '26
ws1.Range("E34").ClearContents '27
ws1.Range("E36").ClearContents '28
ws1.Range("K37").ClearContents '29
ws1.Range("K39").ClearContents '30
ws1.Range("E42").ClearContents '31
ws1.Range("E43").ClearContents '32
ws1.Range("E44").ClearContents '33
ws1.Range("E45").ClearContents '34
'Section c
ws1.Range("C48").ClearContents '35
ws1.Range("H48").ClearContents '36
ws1.Range("M48").ClearContents '37
ws1.Range("K51").ClearContents '38
ws1.Range("K52").ClearContents '39
ws1.Range("K53").ClearContents '40
ws1.Range("K56").ClearContents '41
ws1.Range("K57").ClearContents '42
ws1.Range("K58").ClearContents '43
ws1.Range("K59").ClearContents '44
ws1.Range("K60").ClearContents '45
ws1.Range("E63").ClearContents '46
ws1.Range("K64").ClearContents '47
ws1.Range("E65").ClearContents '48
'ws1.Range("I65") = ?? 'What about I65?
ws1.Range("E66").ClearContents '49
ws1.Range("K69").ClearContents '50
ws1.Range("K71").ClearContents '51

'Sample data did not include these fields
'ws1.Range("K73").ClearContents  '52
'ws1.Range("K82").ClearContents  '53
'ws1.Range("K84").ClearContents  '54
'ws1.Range("K85").ClearContents  '55
'ws1.Range("K86").ClearContents  '56
'ws1.Range("K88").ClearContents  '57
'ws1.Range("K89").ClearContents  '58
'ws1.Range("K92").ClearContents  '59
'ws1.Range("K93").ClearContents  '60
'ws1.Range("K96").ClearContents  '61
'ws1.Range("K98").ClearContents  '62
'ws1.Range("K109").ClearContents  '63
'ws1.Range("K111").ClearContents  '64
'ws1.Range("K112").ClearContents  '65
'ws1.Range("K114").ClearContents  '66
'ws1.Range("K116").ClearContents  '67
'ws1.Range("K117").ClearContents  '68
'ws1.Range("K118").ClearContents  '69

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

Similar threads

S
  • Question Question
What's the best/most efficient way to set Variables for Columns
Replies
9
Views
296
dmt32
D
O
  • Question Question
I am new to VBA and I am trying to figure out how I can use VBA to fill out an online risk calculator using data from excel
Replies
2
Views
318
John_w
J
G
  • Question Question
Format Pulled Data from Workbook into a Text Box
Replies
6
Views
164
Fuji
F
M
  • Question Question
Populate a table of items based on 1 cell selection
Replies
1
Views
108
Edgar_
Edgar_
A
  • Question Question
Modifying code for message box on userform based on selected combobox
Replies
0
Views
96
Ali M
A

Forum statistics

Threads
1,225,060
Messages
6,182,614
Members
453,127
Latest member
IMagill

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