Formula is being cleared in input form when data is cleared for next user

B

bmoses09

New Member
Joined
Oct 20, 2010
Messages
11
Hi, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
I have a bit of a problem with a formula which is part of my input survey form. The form was not made using the form wizard and is just a spreadsheet with drop down menus and blank cells.<o:p></o:p>
Once the user inputs the data they click on a input button which has the following macro attached:<o:p></o:p>
Sub MacroMacro()<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
Worksheets("MASTERSHEET").Range(" A2 ").Value = Worksheets("INPUT FORM").Range(" B2 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" B2 ").Value = Worksheets("INPUT FORM").Range(" B3 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" C2 ").Value = Worksheets("INPUT FORM").Range(" B4 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" D2 ").Value = Worksheets("INPUT FORM").Range(" B5 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" E2 ").Value = Worksheets("INPUT FORM").Range(" B6 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" F2 ").Value = Worksheets("INPUT FORM").Range(" B7 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" G2 ").Value = Worksheets("INPUT FORM").Range(" B8 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" H2 ").Value = Worksheets("INPUT FORM").Range(" B9 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" I2 ").Value = Worksheets("INPUT FORM").Range(" B10 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" J2 ").Value = Worksheets("INPUT FORM").Range(" B11 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" K2 ").Value = Worksheets("INPUT FORM").Range(" B12 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" L2 ").Value = Worksheets("INPUT FORM").Range(" B13 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" M2 ").Value = Worksheets("INPUT FORM").Range(" B14 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" N2 ").Value = Worksheets("INPUT FORM").Range(" B15 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" O2 ").Value = Worksheets("INPUT FORM").Range(" B16 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" P2 ").Value = Worksheets("INPUT FORM").Range(" B17 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" Q2 ").Value = Worksheets("INPUT FORM").Range(" B18 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" R2 ").Value = Worksheets("INPUT FORM").Range(" B19 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" S2 ").Value = Worksheets("INPUT FORM").Range(" B20 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" T2 ").Value = Worksheets("INPUT FORM").Range(" B21 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" U2 ").Value = Worksheets("INPUT FORM").Range(" B22 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" V2 ").Value = Worksheets("INPUT FORM").Range(" B23 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" W2 ").Value = Worksheets("INPUT FORM").Range(" B24 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" X2 ").Value = Worksheets("INPUT FORM").Range(" B25 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" Y2 ").Value = Worksheets("INPUT FORM").Range(" B26 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" Z2 ").Value = Worksheets("INPUT FORM").Range(" B27 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AA2 ").Value = Worksheets("INPUT FORM").Range(" B28 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AB2 ").Value = Worksheets("INPUT FORM").Range(" B29 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AC2 ").Value = Worksheets("INPUT FORM").Range(" B30 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AD2 ").Value = Worksheets("INPUT FORM").Range(" B31 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AE2 ").Value = Worksheets("INPUT FORM").Range(" B32 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AF2 ").Value = Worksheets("INPUT FORM").Range(" B33 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AG2 ").Value = Worksheets("INPUT FORM").Range(" B34 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AH2 ").Value = Worksheets("INPUT FORM").Range(" B35 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AI2 ").Value = Worksheets("INPUT FORM").Range(" B36 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AJ2 ").Value = Worksheets("INPUT FORM").Range(" B37 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AK2 ").Value = Worksheets("INPUT FORM").Range(" B38 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AL2 ").Value = Worksheets("INPUT FORM").Range(" B39 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AM2 ").Value = Worksheets("INPUT FORM").Range(" B40 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AN2 ").Value = Worksheets("INPUT FORM").Range(" B41 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AO2 ").Value = Worksheets("INPUT FORM").Range(" B42 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AP2 ").Value = Worksheets("INPUT FORM").Range(" B43 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AQ2 ").Value = Worksheets("INPUT FORM").Range(" B44 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AR2 ").Value = Worksheets("INPUT FORM").Range(" B45 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AS2 ").Value = Worksheets("INPUT FORM").Range(" B46 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AT2 ").Value = Worksheets("INPUT FORM").Range(" B47 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AU2 ").Value = Worksheets("INPUT FORM").Range(" B48 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AV2 ").Value = Worksheets("INPUT FORM").Range(" B49 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AW2 ").Value = Worksheets("INPUT FORM").Range(" B50 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AX2 ").Value = Worksheets("INPUT FORM").Range(" B51 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AY2 ").Value = Worksheets("INPUT FORM").Range(" B52 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" AZ2 ").Value = Worksheets("INPUT FORM").Range(" B53 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BA2 ").Value = Worksheets("INPUT FORM").Range(" B54 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BB2 ").Value = Worksheets("INPUT FORM").Range(" B55 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BC2 ").Value = Worksheets("INPUT FORM").Range(" B56 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BD2 ").Value = Worksheets("INPUT FORM").Range(" B57 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BE2 ").Value = Worksheets("INPUT FORM").Range(" B58 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BF2 ").Value = Worksheets("INPUT FORM").Range(" B59 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BG2 ").Value = Worksheets("INPUT FORM").Range(" B60 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BH2 ").Value = Worksheets("INPUT FORM").Range(" B61 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BI2 ").Value = Worksheets("INPUT FORM").Range(" B62 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BJ2 ").Value = Worksheets("INPUT FORM").Range(" B63 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BK2 ").Value = Worksheets("INPUT FORM").Range(" B64 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BL2 ").Value = Worksheets("INPUT FORM").Range(" B65 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BM2 ").Value = Worksheets("INPUT FORM").Range(" B66 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BN2 ").Value = Worksheets("INPUT FORM").Range(" B67 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BO2 ").Value = Worksheets("INPUT FORM").Range(" B68 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BP2 ").Value = Worksheets("INPUT FORM").Range(" B69 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BQ2 ").Value = Worksheets("INPUT FORM").Range(" B70 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BR2 ").Value = Worksheets("INPUT FORM").Range(" B71 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BS2 ").Value = Worksheets("INPUT FORM").Range(" B72 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BT2 ").Value = Worksheets("INPUT FORM").Range(" B73 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BU2 ").Value = Worksheets("INPUT FORM").Range(" B74 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BV2 ").Value = Worksheets("INPUT FORM").Range(" B75 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BW2 ").Value = Worksheets("INPUT FORM").Range(" B76 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BX2 ").Value = Worksheets("INPUT FORM").Range(" B77 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BY2 ").Value = Worksheets("INPUT FORM").Range(" B78 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" BZ2 ").Value = Worksheets("INPUT FORM").Range(" B79 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CA2 ").Value = Worksheets("INPUT FORM").Range(" B80 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CB2 ").Value = Worksheets("INPUT FORM").Range(" B81 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CC2 ").Value = Worksheets("INPUT FORM").Range(" B82 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CD2 ").Value = Worksheets("INPUT FORM").Range(" B83 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CE2 ").Value = Worksheets("INPUT FORM").Range(" B84 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CF2 ").Value = Worksheets("INPUT FORM").Range(" B85 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CG2 ").Value = Worksheets("INPUT FORM").Range(" B86 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CH2 ").Value = Worksheets("INPUT FORM").Range(" B87 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CI2 ").Value = Worksheets("INPUT FORM").Range(" B88 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CJ2 ").Value = Worksheets("INPUT FORM").Range(" B89 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CK2 ").Value = Worksheets("INPUT FORM").Range(" B90 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CL2 ").Value = Worksheets("INPUT FORM").Range(" B91 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CM2 ").Value = Worksheets("INPUT FORM").Range(" B92 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CN2 ").Value = Worksheets("INPUT FORM").Range(" B93 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CO2 ").Value = Worksheets("INPUT FORM").Range(" B94 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CP2 ").Value = Worksheets("INPUT FORM").Range(" B95 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CQ2 ").Value = Worksheets("INPUT FORM").Range(" B96 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CR2 ").Value = Worksheets("INPUT FORM").Range(" B97 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CS2 ").Value = Worksheets("INPUT FORM").Range(" B98 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CT2 ").Value = Worksheets("INPUT FORM").Range(" B99 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CU2 ").Value = Worksheets("INPUT FORM").Range(" B100 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CV2 ").Value = Worksheets("INPUT FORM").Range(" B101 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CW2 ").Value = Worksheets("INPUT FORM").Range(" B102 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CX2 ").Value = Worksheets("INPUT FORM").Range(" B103 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CY2 ").Value = Worksheets("INPUT FORM").Range(" B104 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" CZ2 ").Value = Worksheets("INPUT FORM").Range(" B105 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" DA2 ").Value = Worksheets("INPUT FORM").Range(" B106 ")<o:p></o:p>
Worksheets("MASTERSHEET").Range(" DB2 ").Value = Worksheets("INPUT FORM").Range(" B107 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" A2 ").Value = Worksheets("INPUT FORM").Range(" B2 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" B2 ").Value = Worksheets("INPUT FORM").Range(" B3 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" C2 ").Value = Worksheets("INPUT FORM").Range(" B4 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" D2 ").Value = Worksheets("INPUT FORM").Range(" B5 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" E2 ").Value = Worksheets("INPUT FORM").Range(" B6 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" F2 ").Value = Worksheets("INPUT FORM").Range(" B7 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" G2 ").Value = Worksheets("INPUT FORM").Range(" B8 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" H2 ").Value = Worksheets("INPUT FORM").Range(" B9 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" I2 ").Value = Worksheets("INPUT FORM").Range(" B10 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" J2 ").Value = Worksheets("INPUT FORM").Range(" B11 ")<o:p></o:p>
Worksheets("PROP INFO").Range(" K2 ").Value = Worksheets("INPUT FORM").Range(" B12 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" A2 ").Value = Worksheets("INPUT FORM").Range(" B2 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" B2 ").Value = Worksheets("INPUT FORM").Range(" B3 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" C2 ").Value = Worksheets("INPUT FORM").Range(" B4 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" D2 ").Value = Worksheets("INPUT FORM").Range(" B5 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" E2 ").Value = Worksheets("INPUT FORM").Range(" B6 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" F2 ").Value = Worksheets("INPUT FORM").Range(" B7 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" G2 ").Value = Worksheets("INPUT FORM").Range(" B8 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" H2 ").Value = Worksheets("INPUT FORM").Range(" B9 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" I2 ").Value = Worksheets("INPUT FORM").Range(" B10 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" J2 ").Value = Worksheets("INPUT FORM").Range(" B11 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" K2 ").Value = Worksheets("INPUT FORM").Range(" B12 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" L2 ").Value = Worksheets("INPUT FORM").Range(" B13 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" M2 ").Value = Worksheets("INPUT FORM").Range(" B14 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" N2 ").Value = Worksheets("INPUT FORM").Range(" B15 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" O2 ").Value = Worksheets("INPUT FORM").Range(" B16 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" P2 ").Value = Worksheets("INPUT FORM").Range(" B17 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" Q2 ").Value = Worksheets("INPUT FORM").Range(" B18 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" R2 ").Value = Worksheets("INPUT FORM").Range(" B19 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" S2 ").Value = Worksheets("INPUT FORM").Range(" B20 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" T2 ").Value = Worksheets("INPUT FORM").Range(" B21 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" U2 ").Value = Worksheets("INPUT FORM").Range(" B22 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" V2 ").Value = Worksheets("INPUT FORM").Range(" B23 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" W2 ").Value = Worksheets("INPUT FORM").Range(" B24 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" X2 ").Value = Worksheets("INPUT FORM").Range(" B25 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" Y2 ").Value = Worksheets("INPUT FORM").Range(" B26 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" Z2 ").Value = Worksheets("INPUT FORM").Range(" B27 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AA2 ").Value = Worksheets("INPUT FORM").Range(" B28 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AB2 ").Value = Worksheets("INPUT FORM").Range(" B29 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AC2 ").Value = Worksheets("INPUT FORM").Range(" B30 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AD2 ").Value = Worksheets("INPUT FORM").Range(" B31 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AE2 ").Value = Worksheets("INPUT FORM").Range(" B32 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AF2 ").Value = Worksheets("INPUT FORM").Range(" B33 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AG2 ").Value = Worksheets("INPUT FORM").Range(" B34 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AH2 ").Value = Worksheets("INPUT FORM").Range(" B35 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AI2 ").Value = Worksheets("INPUT FORM").Range(" B36 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AJ2 ").Value = Worksheets("INPUT FORM").Range(" B37 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AK2 ").Value = Worksheets("INPUT FORM").Range(" B38 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AL2 ").Value = Worksheets("INPUT FORM").Range(" B39 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AM2 ").Value = Worksheets("INPUT FORM").Range(" B40 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AN2 ").Value = Worksheets("INPUT FORM").Range(" B41 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AO2 ").Value = Worksheets("INPUT FORM").Range(" B42 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AP2 ").Value = Worksheets("INPUT FORM").Range(" B43 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AQ2 ").Value = Worksheets("INPUT FORM").Range(" B44 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AR2 ").Value = Worksheets("INPUT FORM").Range(" B45 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AS2 ").Value = Worksheets("INPUT FORM").Range(" B46 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AT2 ").Value = Worksheets("INPUT FORM").Range(" B47 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AU2 ").Value = Worksheets("INPUT FORM").Range(" B48 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AV2 ").Value = Worksheets("INPUT FORM").Range(" B49 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AW2 ").Value = Worksheets("INPUT FORM").Range(" B50 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AX2 ").Value = Worksheets("INPUT FORM").Range(" B51 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AY2 ").Value = Worksheets("INPUT FORM").Range(" B52 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" AZ2 ").Value = Worksheets("INPUT FORM").Range(" B53 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" BA2 ").Value = Worksheets("INPUT FORM").Range(" B54 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" BB2 ").Value = Worksheets("INPUT FORM").Range(" B55 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" BC2 ").Value = Worksheets("INPUT FORM").Range(" B56 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" BD2 ").Value = Worksheets("INPUT FORM").Range(" B57 ")<o:p></o:p>
Worksheets("EXTERNAL").Range(" BE2 ").Value = Worksheets("INPUT FORM").Range(" B58 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" A2 ").Value = Worksheets("INPUT FORM").Range(" B2 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" B2 ").Value = Worksheets("INPUT FORM").Range(" B3 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" C2 ").Value = Worksheets("INPUT FORM").Range(" B4 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" D2 ").Value = Worksheets("INPUT FORM").Range(" B5 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" E2 ").Value = Worksheets("INPUT FORM").Range(" B6 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" F2 ").Value = Worksheets("INPUT FORM").Range(" B59 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" G2 ").Value = Worksheets("INPUT FORM").Range(" B60 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" H2 ").Value = Worksheets("INPUT FORM").Range(" B61 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" I2 ").Value = Worksheets("INPUT FORM").Range(" B62 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" J2 ").Value = Worksheets("INPUT FORM").Range(" B63 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" K2 ").Value = Worksheets("INPUT FORM").Range(" B64 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" L2 ").Value = Worksheets("INPUT FORM").Range(" B65 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" M2 ").Value = Worksheets("INPUT FORM").Range(" B66 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" N2 ").Value = Worksheets("INPUT FORM").Range(" B67 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" O2 ").Value = Worksheets("INPUT FORM").Range(" B68 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" P2 ").Value = Worksheets("INPUT FORM").Range(" B69 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" Q2 ").Value = Worksheets("INPUT FORM").Range(" B70 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" R2 ").Value = Worksheets("INPUT FORM").Range(" B71 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" S2 ").Value = Worksheets("INPUT FORM").Range(" B72 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" T2 ").Value = Worksheets("INPUT FORM").Range(" B73 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" U2 ").Value = Worksheets("INPUT FORM").Range(" B74 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" V2 ").Value = Worksheets("INPUT FORM").Range(" B75 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" W2 ").Value = Worksheets("INPUT FORM").Range(" B76 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" X2 ").Value = Worksheets("INPUT FORM").Range(" B77 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" Y2 ").Value = Worksheets("INPUT FORM").Range(" B78 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" Z2 ").Value = Worksheets("INPUT FORM").Range(" B79 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AA2 ").Value = Worksheets("INPUT FORM").Range(" B80 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AB2 ").Value = Worksheets("INPUT FORM").Range(" B81 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AC2 ").Value = Worksheets("INPUT FORM").Range(" B82 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AD2 ").Value = Worksheets("INPUT FORM").Range(" B83 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AE2 ").Value = Worksheets("INPUT FORM").Range(" B84 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AF2 ").Value = Worksheets("INPUT FORM").Range(" B85 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AG2 ").Value = Worksheets("INPUT FORM").Range(" B86 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AH2 ").Value = Worksheets("INPUT FORM").Range(" B87 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AI2 ").Value = Worksheets("INPUT FORM").Range(" B88 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AJ2 ").Value = Worksheets("INPUT FORM").Range(" B89 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AK2 ").Value = Worksheets("INPUT FORM").Range(" B90 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AL2 ").Value = Worksheets("INPUT FORM").Range(" B91 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AM2 ").Value = Worksheets("INPUT FORM").Range(" B92 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AN2 ").Value = Worksheets("INPUT FORM").Range(" B93 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AO2 ").Value = Worksheets("INPUT FORM").Range(" B94 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AP2 ").Value = Worksheets("INPUT FORM").Range(" B95 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AQ2 ").Value = Worksheets("INPUT FORM").Range(" B96 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AR2 ").Value = Worksheets("INPUT FORM").Range(" B97 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AS2 ").Value = Worksheets("INPUT FORM").Range(" B98 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AT2 ").Value = Worksheets("INPUT FORM").Range(" B99 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AU2 ").Value = Worksheets("INPUT FORM").Range(" B100 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AV2 ").Value = Worksheets("INPUT FORM").Range(" B101 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AW2 ").Value = Worksheets("INPUT FORM").Range(" B102 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AX2 ").Value = Worksheets("INPUT FORM").Range(" B103 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AY2 ").Value = Worksheets("INPUT FORM").Range(" B104 ")<o:p></o:p>
Worksheets("INTERNAL").Range(" AZ2 ").Value = Worksheets("INPUT FORM").Range(" B105 ")<o:p></o:p>
<o:p> </o:p>
Sheets("INPUT FORM").Range("B2:B107").ClearContents<o:p></o:p>
ActiveWorkbook.Save<o:p></o:p>
MsgBox "Survey data added.", vbInformation<o:p></o:p>
Application.ScreenUpdating = True<o:p></o:p>
<o:p> </o:p>
End Sub<o:p></o:p>
It takes the information inputted into different worksheets then clears the form for the next record. <o:p></o:p>
However, the problem is that there is a vlookup formula, which looks up an address when its Unique reference is typed. When the user clicks to enter the record , the formula is gone for the next user. <o:p></o:p>
Has anybody got any suggestions ?<o:p></o:p>
I’m using excel 2007 btw.
Thanks :)<o:p></o:p>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
I imagine that's because of:

Sheets("INPUT FORM").Range("B2:B107").ClearContents<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

assuming a cell in that range contains the formula. If that's in B10 try:

Sheets("INPUT FORM").Range("B2:B9,B11:B107").ClearContents<o:p></o:p>
 
Upvote 0
Just as a tip the bulk of that macro could be trimmed down

Code: 
Dim i As Integer
Dim j As Integer
Dim c As Integer
Dim Sht As String
Dim InpSht As Worksheet
Set InpSht = Sheets("INPUT FORM")
For i = 1 To 4
    Select Case i
        Case 1: c = 106
                Sht = "MASTERSHEET"
        Case 2: c = 11
                Sht = "PROPINFO"
        Case 3: c = 57
                Sht = "EXTERNAL"
        Case 4: c = 104
                Sht = "INTERNAL"
    End Select
    
    For j = 1 To c
    
        Sheets(Sht).Cells(2, j) = InpSht.Range("B" & j + 1)
    Next j
Next i

Could replace all the

Code: 
Worksheets("MASTERSHEET").Range(" A2 ").Value = Worksheets("INPUT FORM").Range(" B2 ")
.
.
.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Martin_H
  • Solved
Simplify macro [VBA]
Replies
4
Views
444
Fluff
Fluff
PuntingJawa
  • Solved
Copying Sheet from one Workbook to another.
3 4 5
Replies
48
Views
3K
johnnyL
J
M
  • Question Question
VBS Crash
Replies
1
Views
758
Alex Blakenburg
A
J
  • Question Question
runtime 1004 object worksheet not defined error
Replies
4
Views
409
jordanburch
J
E
  • Solved
How to select data in a row that is higher/lower using if problem
Replies
3
Views
383
etaf
etaf

Forum statistics

Threads
1,223,705
Messages
6,173,996
Members
452,542
Latest member
Bricklin

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