maraecole3
07-03-2019, 07:44 AM
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
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