Transferring Data from Multipage Userform to Separate, Subsequent Rows in Excel

[maraecole3]

Hi,

I am struggling trying to transfer data from 4 multipages through 3 sheets. My sequence goes:

Userform (data entry on each page) --> submit --> "Data" sheet which would populate each row with one multipage entry (This is one roadblock)--> "SampleResult" sheet which is formatted like a userform but is not. It has 4 chunks of repeated format where each chunk, B-L, Q-AA, AF-AP, and AE-BU, corresponds to each multpage. So multipage1=0's data should populate 1 row which goes to B-L. Multipage1 =1 should populate row 2 which goes to Q - AA and etc but in very specific cells. --> Then those specific cells are copy/pasted to "USRMResultsAnalysis" (Assuming this will be another roadblock)sheet on a line by line format like a ledger but with the results from the calculations done on "SampleResult". After it is all said and done, I get a pdf of SampleResult (which I got to work, YAY).

I have tried copy/paste, copy/destination, assigning directly to cells, irow+1,2,3,4, replicating each sheet and using queries to compile them back to the "Data" sheet, prayer, forums, google, and Youtube.

Here is the code I have for the Save command which initiates all of this foolery. By the way, I got it to work with one but cannot get it to work with 4. And, currently, I think it is overlapping the same row with each multipage data because I end up with only 1 line of data but from page4.


I want it to do this (unless there is a much easier way)
Excel Row# Ledger Number Sample ID (multipage reference)
1 1 1-A
2 1 1-B
3 1 1-C
4 1 1-D
5 2 2-A (and continues)

I uploaded both workbooks (Industrial workbook is the one I'm struggling with), the (USDA method is the one I've already figured out and am replicating but x4 at a time). Btw, I am the author of both methods and VBA.
Thank you so much for your help,
Marsha





Here is an example with just two pages:

Private Sub cmdSave_Click()
Application.ScreenUpdating = False        
    Dim iRow As Long
    Dim Path As String
    Dim ws1 As Worksheet 
    ' Transferring each textbox from the userform to the "Data" sheet to be on different lines. All of the textboxes among the multipages _
    ' have a similar name except they are distinguished by the last digit on the textbox/combobox from page 2 through  4. If it is outside _
    ' of the multipage and in the main userform space, it doesn't have a number by it.            
    iRow = ThisWorkbook.Sheets("Data").Range("A1048576").End(xlUp).row + 1    
    If ValidateForm = True Then
        With ThisWorkbook.Sheets("Data").          
             .Range("A" & iRow).Value = (iRow - 2)
             .Range("B" & iRow).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
             .Range("C" & iRow).Value = txtTechName.Value
             .Range("D" & iRow).Value = txtProjName.Value
             .Range("E" & iRow).Value = txtSampleName1.Value
             .Range("F" & iRow).Value = Me.ComboBox1.Value
             .Range("G" & iRow).Value = IIf(optRep1.Value = True, "  1", " 2")
             .Range("H" & iRow).Value = txtSugarMass.Value
             .Range("I" & iRow).Value = txtBrix1.Value
             .Range("J" & iRow).Value = txtBrix2.Value
             .Range("K" & iRow).Value = txtBrix3.Value
             .Range("L" & iRow).Value = txtPreMw.Value
             .Range("M" & iRow).Value = txtPostMw.Value
             .Range("N" & iRow).Value = txtCorrMw.Value
             .Range("O" & iRow).Value = txtPreSon.Value
             .Range("P" & iRow).Value = txtPostSon.Value
             .Range("Q" & iRow).Value = txtCorrSon.Value
             .Range("R" & iRow).Value = txtTrxAbs1.Value
             .Range("S" & iRow).Value = txtTrxAbs2.Value
             .Range("T" & iRow).Value = txtTrxCB1.Value
             .Range("U" & iRow).Value = txtTDF1.Value
             .Range("V" & iRow).Value = txtTrxIB.Value
             .Range("W" & iRow).Value = txtNoTrxAbs1.Value
             .Range("X" & iRow).Value = txtNoTrxAbs2.Value
             .Range("Y" & iRow).Value = txtNoTrxCB1.Value
             .Range("Z" & iRow).Value = txtNDF1.Value
             .Range("AA" & iRow).Value = txtNoTrxIB.Value
             .Range("AB" & iRow).Value = TextBox76.Value
          Next iRow 
          .Range("A" & iRow).Value = (iRow - 2)
          .Range("B" & iRow).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
          .Range("C" & iRow).Value = txtTechName.Value
          .Range("D" & iRow).Value = txtProjName.Value
          .Range("E" & iRow).Value = txtSampleName2.Value
          .Range("F" & iRow).Value = Me.ComboBox2.Value
          .Range("G" & iRow).Value = IIf(optRep12.Value = True, "  1", " 2")
          .Range("H" & iRow).Value = txtSugarMass2.Value
          .Range("I" & iRow).Value = txtBrix12.Value
          .Range("J" & iRow).Value = txtBrix22.Value
          .Range("K" & iRow).Value = txtBrix32.Value
          .Range("L" & iRow).Value = txtPreMw2.Value
          .Range("M" & iRow).Value = txtPostMw2.Value
          .Range("N" & iRow).Value = txtCorrMw2.Value
          .Range("O" & iRow).Value = txtPreSon2.Value
          .Range("P" & iRow).Value = txtPostSon2.Value
          .Range("Q" & iRow).Value = txtCorrSon2.Value
          .Range("R" & iRow).Value = txtTrxAbs12.Value
          .Range("S" & iRow).Value = txtTrxAbs22.Value
          .Range("T" & iRow).Value = txtTrxCB2.Value
          .Range("U" & iRow).Value = txtTDF2.Value
          .Range("V" & iRow).Value = txtTrxIB2.Value
          .Range("W" & iRow).Value = txtNoTrxAbs12.Value
          .Range("X" & iRow).Value = txtNoTrxAbs22.Value
          .Range("Y" & iRow).Value = txtNoTrxCB2.Value
          .Range("Z" & iRow).Value = txtNDF2.Value
          .Range("AA" & iRow).Value = txtNoTrxIB2.Value
          .Range("AB" & iRow).Value = TextBox76.Value           
          Dim X As Workbook
          Dim Y As Workbook
          Dim strPath As String
          Dim strfolderpath As String
          Dim z As Integer
          Dim numRows As Long
          Application.ScreenUpdating = False
          ' Set numrows = number of rows of data.
          numRows = Range("A1", Range("A1").End(xlDown)).Rows.count
           ' Select cell a1.
           Range("A3").Select
           ' Set loop
           For z = 3 To numRows  ' Starts at row 3 because my first two rows are headers.
                ' Flask 1 (This  is the information from page 1 that should be transferred from "Data" Sheet's respective rows to _
                ' "SampleResult" sheet in specific cells to do the math I need. I also print to pdf this sheet when all done and got that working.
                ' Copy RecordLocator from x:
                ThisWorkbook.Sheets("Data").Cells(z, 1).Copy 'A3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("C2").PasteSpecial                    
                ' Copy Date from x:
                ThisWorkbook.Sheets("Data").Cells(z, 2).Copy 'B3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("C4").PasteSpecial
                ' Copy TechName from x:
                ThisWorkbook.Sheets("Data").Cells(z, 3).Copy 'C3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("C5").PasteSpecial                    
                ' Copy Project Name from x:
                ThisWorkbook.Sheets("Data").Cells(z, 4).Copy 'D3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("H1").PasteSpecial                    
                ' Copy Sample Name from x:
                ThisWorkbook.Sheets("Data").Cells(z, 5).Copy 'E3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("c1").PasteSpecial
                ' Copy Sample Type from x:
                ThisWorkbook.Sheets("Data").Cells(z, 6).Copy 'F3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("C3").PasteSpecial                   
                ' Copy Replicate Number from x:
                ThisWorkbook.Sheets("Data").Cells(z, 7).Copy 'G3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("C6").PasteSpecial                    
                ' Copy Sugar Mass from x:
                ThisWorkbook.Sheets("Data").Cells(z, 8).Copy 'H3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("C7").PasteSpecial                                      
                ' Copy Brix 1 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 9).Copy 'i3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("G4").PasteSpecial                  
                ' Copy Brix 2 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 10).Copy 'J3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("H4").PasteSpecial                    
                ' Copy Brix 3 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 11).Copy 'K3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("I4").PasteSpecial                   
                ' Copy MwPre from x:
                ThisWorkbook.Sheets("Data").Cells(z, 12).Copy 'L3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("e12").PasteSpecial                    
                ' Copy MwPost from x:
                ThisWorkbook.Sheets("Data").Cells(z, 13).Copy 'M3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("E13").PasteSpecial                   
                ' Copy MwCorr from x:
                ThisWorkbook.Sheets("Data").Cells(z, 14).Copy 'N3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("E14").PasteSpecial                   
                ' Copy SonPre from x:
                ThisWorkbook.Sheets("Data").Cells(z, 15).Copy 'o3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("I12").PasteSpecial                   
                ' Copy SonPost from x:
                ThisWorkbook.Sheets("Data").Cells(z, 16).Copy 'p3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("i13").PasteSpecial                   
                ' Copy SonCorr from x:
                ThisWorkbook.Sheets("Data").Cells(z, 17).Copy 'q3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("i14").PasteSpecial                   
                ' Copy TrxAbs1 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 18).Copy 'R3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("D20").PasteSpecial                                         
                ' Copy TrxAbs2 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 19).Copy 'S3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("E20").PasteSpecial                   
                ' Copy TrxCB1 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 20).Copy 'T3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("C20").PasteSpecial                    
                ' Copy TrxCB2 from x
                ThisWorkbook.Sheets("Data").Cells(z, 21).Copy 'u3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("E21").PasteSpecial                    
                ' Copy IB1 from x
                ThisWorkbook.Sheets("Data").Cells(z, 22).Copy 'V3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("D22").PasteSpecial                   
                ' Copy NoTrxAbs1 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 23).Copy 'W3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("G20").PasteSpecial                                         
                ' Copy NoTrxAbs2 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 24).Copy 'X3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("H20").PasteSpecial                   
                ' Copy NoTrxCB1 from x:
                ThisWorkbook.Sheets("Data").Cells(z, 25).Copy 'Y3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("F20").PasteSpecial                    
                ' Copy TrxCB2 from x
                ThisWorkbook.Sheets("Data").Cells(z, 26).Copy 'Z3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("H21").PasteSpecial                   
                ' Copy IB1 from x
                ThisWorkbook.Sheets("Data").Cells(z, 27).Copy 'AA3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("G22").PasteSpecial                                      
                ' Copy Method Time from x
                ThisWorkbook.Sheets("Data").Cells(z, 28).Copy 'AB3
                ' Paste to y worksheet:
                ThisWorkbook.Sheets("SampleResult").Range("K5").PasteSpecial
                  
'Flask 2 (This  is the information from page 2 that should be transferred from "Data" Sheet's respective rows to "SampleResult" sheet in specific cells to do the math I need. I also print to pdf this sheet when all done and got that working.


                       'copy RecordLocator from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 1).Copy 'A3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("R2").PasteSpecial                    
                    'copy Date from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 2).Copy 'B3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("R4").PasteSpecial                  
                    'copy TechName from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 3).Copy 'C3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("R5").PasteSpecial                    
                    'copy Project Name from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 4).Copy 'D3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("W1").PasteSpecial                    
                    'copy Sample Name from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 5).Copy 'E3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("R1").PasteSpecial                    
                    'copy Sample Type from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 6).Copy 'F3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("R3").PasteSpecial                    
                    'copy Replicate Number from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 7).Copy 'G3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("R6").PasteSpecial                    
                    'copy Sugar Mass from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 8).Copy 'H3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("R7").PasteSpecial                                       
                    'copy Brix 1 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 9).Copy 'i3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("V4").PasteSpecial                  
                    'copy Brix 2 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 10).Copy 'J3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("W4").PasteSpecial                    
                    'copy Brix 3 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 11).Copy 'K3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("X4").PasteSpecial                    
                    'copy MwPre from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 12).Copy 'L3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("T12").PasteSpecial                    
                    'copy MwPost from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 13).Copy 'M3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("T13").PasteSpecial                    
                    'copy MwCorr from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 14).Copy 'N3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("T14").PasteSpecial                   
                    'copy SonPre from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 15).Copy 'o3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("X12").PasteSpecial                    
                    'copy SonPost from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 16).Copy 'p3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("X13").PasteSpecial
                    'copy SonCorr from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 17).Copy 'q3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("X14").PasteSpecial                   
                    'copy TrxAbs1 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 18).Copy 'R3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("S20").PasteSpecial                                          
                     'copy TrxAbs2 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 19).Copy 'S3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("T20").PasteSpecial                    
                    'copy TrxCB1 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 20).Copy 'T3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("R20").PasteSpecial                   
                    'copy TrxCB2 from x
                    ThisWorkbook.Sheets("Data").Cells(z, 21).Copy 'u3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("T21").PasteSpecial                   
                    'copy IB1 from x
                    ThisWorkbook.Sheets("Data").Cells(z, 22).Copy 'V3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("S22").PasteSpecial                   
                    'copy NoTrxAbs1 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 23).Copy 'W3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("V20").PasteSpecial                                          
                     'copy NoTrxAbs2 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 24).Copy 'X3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("W20").PasteSpecial                  
                    'copy NoTrxCB1 from x:
                    ThisWorkbook.Sheets("Data").Cells(z, 25).Copy 'Y3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("U20").PasteSpecial                    
                    'copy TrxCB2 from x
                    ThisWorkbook.Sheets("Data").Cells(z, 26).Copy 'Z3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("W21").PasteSpecial                    
                    'copy IB1 from x
                    ThisWorkbook.Sheets("Data").Cells(z, 27).Copy 'AA3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("V22").PasteSpecial                                       
                    'copy Method Time from x
                    ThisWorkbook.Sheets("Data").Cells(z, 28).Copy 'AB3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("SampleResult").Range("Z5").PasteSpecial
 ThisWorkbook.Sheets("SampleResult").Select  
    'Store results in "USRMResultsAnalysis" Sheet from "SampleResult" Sheet
 
                   
'Flask 1 (This  is the information from page 1 that should be transferred from "SampleResult" Excel Form to "USRMResults" sheet in row1 like in "Data" but with different values.
                    'copy RecordLocator from x:
                    ThisWorkbook.Sheets("SampleResult").Range("C2").Copy 'A3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                   
                    'copy Date from x:
                    ThisWorkbook.Sheets("SampleResult").Range("C4").Copy 'B3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                   
                    'copy TechName from x:
                    ThisWorkbook.Sheets("SampleResult").Range("C5").Copy 'C3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Project Name from x:
                    ThisWorkbook.Sheets("SampleResult").Range("H1").Copy 'D3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 4).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                   
                    'copy Sample Name from x:
                    ThisWorkbook.Sheets("SampleResult").Range("c1").Copy 'E3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Sample Type from x:
                    ThisWorkbook.Sheets("SampleResult").Range("C3").Copy 'F3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 6).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Replicate Number from x:
                    ThisWorkbook.Sheets("SampleResult").Range("C6").Copy 'G3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 7).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                   
                    'copy Sugar Mass from x:
                    ThisWorkbook.Sheets("SampleResult").Range("C7").Copy 'H3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 8).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                                      
                    'copy Avg Brix from x:
                    ThisWorkbook.Sheets("SampleResult").Range("g7").Copy 'i3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 9).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy StdevBrix from x:
                    ThisWorkbook.Sheets("SampleResult").Range("h7").Copy 'J3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 10).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                   
                    'copy Total Starch (ppm) 3 from x:
                    ThisWorkbook.Sheets("SampleResult").Range("f31").Copy 'K3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 11).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Starch ppm stdev from x:
                    ThisWorkbook.Sheets("SampleResult").Range("g31").Copy 'L3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 12).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Starch %CV from x:
                    ThisWorkbook.Sheets("SampleResult").Range("H31").Copy 'M3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 13).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                     'copy Total Starch (ppm/Brix) 3 from x:
                    ThisWorkbook.Sheets("SampleResult").Range("F32").Copy 'K3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 14).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Starch ppm stdev from x:
                    ThisWorkbook.Sheets("SampleResult").Range("g32").Copy 'L3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 15).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                     
                    'copy Starch %CV from x:
                    ThisWorkbook.Sheets("SampleResult").Range("H32").Copy 'M3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 16).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy %Soluble Starch from x:
                    ThisWorkbook.Sheets("SampleResult").Range("K41").Copy 'q3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 17).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy %Insoluble Starch from x:
                    ThisWorkbook.Sheets("SampleResult").Range("K42").Copy 'R3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 18).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                 'copy %Insoluble Starch from x:
                    ThisWorkbook.Sheets("SampleResult").Range("K6").Copy 'R3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 19).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

'Flask 2 (This  is the information from page 2 that should be transferred from "SampleResult" Excel Form to "USRMResults" sheet in row2 like in "Data" but with different values.
 
                    'copy RecordLocator from x:
                    ThisWorkbook.Sheets("SampleResult").Range("r2").Copy 'A3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                  
                    'copy Date from x:
                    ThisWorkbook.Sheets("SampleResult").Range("r4").Copy 'B3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                   
                    'copy TechName from x:
                    ThisWorkbook.Sheets("SampleResult").Range("r5").Copy 'C3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                   
                    'copy Project Name from x:
                    ThisWorkbook.Sheets("SampleResult").Range("w1").Copy 'D3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 4).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Sample Name from x:
                    ThisWorkbook.Sheets("SampleResult").Range("r1").Copy 'E3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Sample Type from x:
                    ThisWorkbook.Sheets("SampleResult").Range("r3").Copy 'F3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 6).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Replicate Number from x:
                    ThisWorkbook.Sheets("SampleResult").Range("r6").Copy 'G3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 7).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                   
                    'copy Sugar Mass from x:
                    ThisWorkbook.Sheets("SampleResult").Range("r7").Copy 'H3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 8).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                                       
                    'copy Avg Brix from x:
                    ThisWorkbook.Sheets("SampleResult").Range("v7").Copy 'i3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 9).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy StdevBrix from x:
                    ThisWorkbook.Sheets("SampleResult").Range("w7").Copy 'J3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 10).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Total Starch (ppm) 3 from x:
                    ThisWorkbook.Sheets("SampleResult").Range("u31").Copy 'K3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 11).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Starch ppm stdev from x:
                    ThisWorkbook.Sheets("SampleResult").Range("v31").Copy 'L3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 12).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Starch %CV from x:
                    ThisWorkbook.Sheets("SampleResult").Range("w31").Copy 'M3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 13).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                     'copy Total Starch (ppm/Brix) 3 from x:
                    ThisWorkbook.Sheets("SampleResult").Range("u32").Copy 'K3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 14).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy Starch ppm stdev from x:
                    ThisWorkbook.Sheets("SampleResult").Range("v32").Copy 'L3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 15).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                     
                    'copy Starch %CV from x:
                    ThisWorkbook.Sheets("SampleResult").Range("w32").Copy 'M3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 16).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy %Soluble Starch from x:
                    ThisWorkbook.Sheets("SampleResult").Range("z41").Copy 'q3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 17).PasteSpecial Paste:=xlPasteValuesAndNumberFormats                    
                    'copy %Insoluble Starch from x:
                    ThisWorkbook.Sheets("SampleResult").Range("z42").Copy 'R3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 18).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
                 'copy %Insoluble Starch from x:
                    ThisWorkbook.Sheets("SampleResult").Range("z6").Copy 'R3
                    'paste to y worksheet:
                    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 19).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  Next
        Call Reset
        Application.ScreenUpdating = True
        Exit Sub    
        Application.ScreenUpdating = True
' Next ID number should start here
  StarchAnalysisWkst.TextBox3 = "Run -" & NextNumber(Worksheets("Data").Range("A3:A65536")) + 1
        Me.txtFile2.Text = "CISM Starch Result - " + Format(Date, "yy-mmm-dd-w") + "-" + TextBox3.Value + "-B"
        Me.txtFile1.Text = "CISM Starch Result - " + Format(Date, "yy-mmm-dd-w") + "-" + TextBox3.Value + "-A"
        StarchAnalysisWkst.TextBox1 = Date
Application.ScreenUpdating = True
Unload StarchAnalysisWkst
End With
End If
End Sub