Consulting

Results 1 to 15 of 15

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

  1. #1

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

    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
    Last edited by Paul_Hossler; 07-05-2019 at 08:25 AM. Reason: Added CODE tags

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Nice workbook, very extensive .. prehaps too extensive for us non-originators to easily go through. It also helps for something like this if you can provide a detailed series of steps to explain how to get to the point where you're stuck

    Howeve, if you have a question about method or technique, I've always found it easier to just make a simple WB that replicates my real one, but is enough to demonstrate the issue / question /problem and get feedback that I can incorporate in my real WB

    I'd suggest you make a WB with a multi-page form, some textboxes, few buttons, and a WS or WSs where you want the data to go in your little sample.

    Might be the fastest way to get feedback, unless someone can go through it faster
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Thank you. I have been slaving away on this workbook and am stuck between excited and irritated at this point. But I will edit my post because I did figure out how to send 4 lines of data to my first spreadsheet...now I just don't know how to send it to the next sheet. I think this may be my more specific question.

    I appreciate you having a look at my attachments. As a rookie, your suggestions and remarks make me blush and motivate me more. I will edit the above now.

  4. #4

    How to copy/paste data from specific rows in excel to specific locations in sheet

    So, I tried to edit/trim my original post and cannot find the button I thought I saw. This leads me here. I have figured out how to submit my userform and it populate on 4 subsequent rows in excel (e.g. 1A, 1B, 1C, and 1D are on rows 1, 2,3,and 4) of the sheet called "Data". SUCCESS!!

    My challenge is now, how to select/activate copy/paste specifically from each row to specified locations? In my mind, I am thinking it should be a loop but have not figured it out quite yet. I have about 27 variables that are transferring over from A to AA for each line, and want it to copy/paste (typewriter style) to random cells like C2, H1, J4, T3, etc.

    A range didn't work (for me anyways) because it is not a carbon copy of the entire line. Here is an example of one line...but imagine this four more times on 4 rows at a time.


    When I hit submit, the Userform goes to "Data" and populates an entire row. This part seems to be operating today.

    ThisWorkbook.Sheets("Data").Activate
    NumRows = Range("A1", Range("A1").End(xlDown)).Rows.count
    ' Select cell a1.
    Range("A1").Select


    Range(z & Rows.count).EntireRow(xlDown).Activate
    ' Set loop?
    These are only two cells (A and B) on the same row. As you can see, they are pasting sporadically (and it gets mores sporadic throughout the other variables).
    'copy RecordLocator from x:
    ThisWorkbook.Sheets("Data").Cells(z, 1).Copy 'A3
    'paste to y worksheet:
    ThisWorkbook.Sheets("SampleResult").Range("av2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    'copy Date from x:
    ThisWorkbook.Sheets("Data").Cells(z, 2).Copy 'B3
    'paste to y worksheet:
    ThisWorkbook.Sheets("SampleResult").Range("av4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    Then, the calculations from "SampleResult" are copy/pasted to "USRMResultsAnalysis" on each row. This is my roadblock.

    ThisWorkbook.Sheets("SampleResult").Select
    z = ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(Rows.count, 1).End(xlUp).Offset(1, 0).row

    'Flask 4
    'copy RecordLocator from x:
    ThisWorkbook.Sheets("SampleResult").Range("av2").Copy 'A3
    'paste to y worksheet:
    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    'copy Date from x:
    ThisWorkbook.Sheets("SampleResult").Range("av4").Copy 'B3
    'paste to y worksheet:
    ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(z, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    I need help getting the vba to go all of one row through each sheet, down to the next row through the sheets, and etc. until all four rows have passed. Then I need to make sure that when the userform is executed, it will add to the next four rows (I think I have that one though) when done. Right now it is copying the first row 4 times and pasting it to USRMResultsAnalysis worksheet and I can't figure out why.

    PLEASE HELP ME! I HAVE COME SOOOOOO FARRRRR!!!


  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Just a guess...

    You probably have to do this ..

    z = ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(Rows.count, 1).End(xlUp).Offset(1, 0).row

    ..before each row is copied to get the next (correct) destination row

    or add an increment (1, 2 or 3) to z, maybe something like

    ThisWorkbook.Sheets("SampleResult").Select   ' <<<<<<<<<<<<<<<<<<<<<<< don't need to select something to act on or with it. also Thisworkbook is probably unnecessary
      z = ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(Rows.count, 1).End(xlUp).Offset(1, 0).row
    
    Sheets("SampleResult").Range("av2").Copy 'A3 
    Sheets("USRMResultsAnalysis").Cells(z, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av4").Copy 'B3
    Sheets("USRMResultsAnalysis").Cells(z, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av2").Copy 'A3
    Sheets("USRMResultsAnalysis").Cells(z+1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av4").Copy 'B3
    Sheets("USRMResultsAnalysis").Cells(z+1, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av2").Copy 'A3
    Sheets("USRMResultsAnalysis").Cells(z+2, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av4").Copy 'B3
    Sheets("USRMResultsAnalysis").Cells(z+2, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av2").Copy 'A3
    Sheets("USRMResultsAnalysis").Cells(z+3, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av4").Copy 'B3
    Sheets("USRMResultsAnalysis").Cells(z+3, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Thanks Paul for the information, I will try to input it and see what happens.

    Right now I am putting all of my "Data" to "SampleResult" sheets in sequence together and then putting the others in sequence afterwards. It is something with the copy and paste business for sure.

    Marsha

  7. #7
    Ok, I did it and something happened, no cigar but I have a revelation. After inputting the "z" line above each pasting row, I noticed that the "SampleResult" page is pulling only from the 1st four lines of the "Data" sheet even though I have input a gazillion. Maybe I am fudging the interpretation of "End(xlUp).Offset(#,0).rowfrom the "z" code?

    All I did is change the # from 1, 2,3,and4 like you suggested and I have a header and 3 of the first lines of data. Should I put xlDown? I will try that next.

  8. #8
    Progress, but I got a object error and a overflow error when xlDown. I also am trying to do negative offsets since it should be starting from the bottom. When I tried the initial variations of what you posted, I was able to see that it is copying over to sample result, it is just not copying the correct rows. It is taking my first 4 rows all the time and not any of the later entries. I am trying to Google how to get around an overflow. It is highlighting this line:

    z = ThisWorkbook.Sheets("SampleResult").Cells(Rows.count, 1).End(xlDown).Offset(-4, 0).row

    I was hoping this line will say, "run to the bottom of the entries in "A" and back it up 4 rows and proceed with that". I hope I am interpreting this correctly. :/

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    z = ThisWorkbook.Sheets("SampleResult").Cells(Rows.count, 1).End(xlDown).Offset(-4, 0).row

    That says to start with row 1048576, go to the end (still 1048576), and then go up 4 rows ( 1048572)

    The usual way is to start at the last row and go up (note the With and the dots)

    Sub test()
        Dim z As Long
        
        With Sheets("SampleResult")
            z = .Cells(.Rows.Count, 1).End(xlUp).Row - 4
        End With
    
        MsgBox z
    
    End Sub
    
    Althougth if you want the last 4 rows, you'd use -3 (not .Offset (-4,0) since that will give you row 22, 23, 24, and 25 in my test

    Capture.JPG


    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10

    Copy and Paste Loop Help

    Hi Paul,

    I've been trying to get this to work and thought maybe I could do a loop and realized that, no, not completely. I wanted to simplify all the copy and paste business with a loop. I counted everything out and think I can make it work. My problem is the red bolded line below. Just some new facts, 1) I can get the userform to populate into 4 subsequent rows in "Data" sheet correctly. 2) Looping 4 data rows in "Data" sheet to "SampleResult" Sheet is still a problem. But since every entry for each row is exactly 12 columns apart (from C1 the first cell), I thought this could work (and free up memory?)?

    Dim X As Workbook
    Dim Y As Workbook
    Dim strPath As String
    Dim strfolderpath As String
    Dim z As Long
    Dim NumRows As Long
    Dim rng As Range
    Dim ii As Long
    Dim LastRow As Long
    Dim wb As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim Offset As Integer
    
    
    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("Data")
    Set sht2 = wb.Sheets("SampleResult")
    
    
    z = ThisWorkbook.Sheets("Data").Cells(Rows.count, 1).End(xlUp).Offset(-3, 0).row
    For i = 0 To 3  <- 4 loops, 1 for each line
    z=z + i
    'This is the beginning of the loop
     Offset = 12 <-- I think it is 12, I highlighted it from the first entry (C2) to the next symmetrical entry (R1) it says 12 on "SampleResult"
     
        ii = sht1.Range("A:A").Find("*", searchdirection:=xlDown).Offset(-3, 0).row
        sht2.Columns(1, Offset).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   <-- Error here so far
        
     'Flask 1
    
    
                        'copy RecordLocator from x:
                        sht2.Range("C2" & ii) = sht1.Cells(z, 1).Value
                        sht2.Range("C4" & ii) = sht1.Cells(z, 2).Value
                        sht2.Range("C5" & ii) = sht1.Cells(z, 3).Value
                        sht2.Range("H1" & ii) = sht1.Cells(z, 4).Value
                        sht2.Range("C1" & ii) = sht1.Cells(z, 5).Value
                        sht2.Range("C3" & ii) = sht1.Cells(z, 6).Value
                        sht2.Range("C6" & ii) = sht1.Cells(z, 7).Value
                        sht2.Range("C7" & ii) = sht1.Cells(z, 8).Value
                        sht2.Range("G4" & ii) = sht1.Cells(z, 9).Value
                        sht2.Range("H4" & ii) = sht1.Cells(z, 10).Value
                        sht2.Range("I4" & ii) = sht1.Cells(z, 11).Value
                        sht2.Range("E12" & ii) = sht1.Cells(z, 12).Value
                        sht2.Range("E13" & ii) = sht1.Cells(z, 13).Value
                        sht2.Range("E14" & ii) = sht1.Cells(z, 14).Value
                        sht2.Range("i12" & ii) = sht1.Cells(z, 15).Value
                        sht2.Range("i13" & ii) = sht1.Cells(z, 16).Value
                        sht2.Range("i14" & ii) = sht1.Cells(z, 17).Value
                        sht2.Range("d20" & ii) = sht1.Cells(z, 18).Value
                        sht2.Range("e20" & ii) = sht1.Cells(z, 19).Value
                        sht2.Range("e21" & ii) = sht1.Cells(z, 20).Value
                        sht2.Range("d21" & ii) = sht1.Cells(z, 20).Value
                        sht2.Range("C20" & ii) = sht1.Cells(z, 21).Value
                        sht2.Range("D22" & ii) = sht1.Cells(z, 22).Value
                        sht2.Range("G20" & ii) = sht1.Cells(z, 23).Value
                        sht2.Range("H20" & ii) = sht1.Cells(z, 24).Value
                        sht2.Range("G21" & ii) = sht1.Cells(z, 25).Value
                        sht2.Range("h21" & ii) = sht1.Cells(z, 25).Value
                        sht2.Range("F20" & ii) = sht1.Cells(z, 26).Value
                        sht2.Range("G22" & ii) = sht1.Cells(z, 27).Value
                        sht2.Range("k5" & ii) = sht1.Cells(z, 28).Value
    
    
    Next i <-- I want the loop to go to the next rows (from the bottom most row going -3, -2, -1, 0) and scoot over 10 rows to paste.
    
    
     ThisWorkbook.Sheets("SampleResult").Select
     'Store results in "USRMResultsAnalysis" Sheet from "SampleResult" Sheet









    Quote Originally Posted by Paul_Hossler View Post
    Just a guess...

    You probably have to do this ..

    z = ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(Rows.count, 1).End(xlUp).Offset(1, 0).row

    ..before each row is copied to get the next (correct) destination row

    or add an increment (1, 2 or 3) to z, maybe something like

    ThisWorkbook.Sheets("SampleResult").Select   ' <<<<<<<<<<<<<<<<<<<<<<< don't need to select something to act on or with it. also Thisworkbook is probably unnecessary
      z = ThisWorkbook.Sheets("USRMResultsAnalysis").Cells(Rows.count, 1).End(xlUp).Offset(1, 0).row
    
    Sheets("SampleResult").Range("av2").Copy 'A3 
    Sheets("USRMResultsAnalysis").Cells(z, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av4").Copy 'B3
    Sheets("USRMResultsAnalysis").Cells(z, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av2").Copy 'A3
    Sheets("USRMResultsAnalysis").Cells(z+1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av4").Copy 'B3
    Sheets("USRMResultsAnalysis").Cells(z+1, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av2").Copy 'A3
    Sheets("USRMResultsAnalysis").Cells(z+2, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av4").Copy 'B3
    Sheets("USRMResultsAnalysis").Cells(z+2, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av2").Copy 'A3
    Sheets("USRMResultsAnalysis").Cells(z+3, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("SampleResult").Range("av4").Copy 'B3
    Sheets("USRMResultsAnalysis").Cells(z+3, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Last edited by Paul_Hossler; 07-05-2019 at 02:45 PM.

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    This is just a concept to suggest a possible approach


    It finds the last cell in Col A with data and uses that and 3 rows above in a 0-3 loop

    In the 0-3 loop, it move data to (for first line) to (2,3), (2,13), (2,23), and (2,33) using iOffset = iOffset + 10


    Option Explicit
    Sub Concept()
        Dim strPath As String
        Dim strfolderpath As String
        Dim LastRow As Long
        
        Dim iStartRowOfLastBlock As Long, iBlockRow As Long
        Dim shtData As Worksheet, shtResult As Worksheet
        Dim iOffset As Long
        
        Set shtData = Sheets("Data")
        Set shtResult = Sheets("SampleResult")
        iStartRowOfLastBlock = shtData.Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row
    
        iOffset = 0
        For iBlockRow = 0 To 3      '    <- 4 loops, 1 for each line
            iStartRowOfLastBlock = iStartRowOfLastBlock + iBlockRow
            'didn't understand this
            'This is the beginning of the loop
            'Offset = 12 <-- I think it is 12, I highlighted it from the first entry (C2) to the next symmetrical entry (R1) it says 12 on "SampleResult"
    
            'ii = sht1.Range("A:A").Find("*", searchdirection:=xlDown).Offset(-3, 0).Row
            'sht2.Columns(1, Offset).PasteSpecial Paste:=xlPasteValuesAndNumberFormats <-- Error here so far
            'Flask 1
            With shtResult  '   I think I know my ABCs but better check
                .Cells(2, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 1).Value
                .Cells(4, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 2).Value
                .Cells(5, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 3).Value
                .Cells(1, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 4).Value
                .Cells(1, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 5).Value
                .Cells(3, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 6).Value
                .Cells(6, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 7).Value
                .Cells(7, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 8).Value
                .Cells(4, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 9).Value
                .Cells(4, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 10).Value
                .Cells(4, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 11).Value
                .Cells(12, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 12).Value
                .Cells(13, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 13).Value
                .Cells(14, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 14).Value
                .Cells(12, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 15).Value
                .Cells(13, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 16).Value
                .Cells(14, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 17).Value
                .Cells(20, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 18).Value
                .Cells(20, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 19).Value
                .Cells(21, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
                .Cells(21, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
                .Cells(20, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 21).Value
                .Cells(22, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 22).Value
                .Cells(20, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 23).Value
                .Cells(20, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 24).Value
                .Cells(21, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
                .Cells(21, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
                .Cells(20, 6 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 26).Value
                .Cells(22, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 27).Value
                .Cells(5, 11 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 28).Value
            End With
            
            iOffset = iOffset + 10
        '---------------------------------------------------------------------------------------------------------------------ROWS or COLUMNS -- I did columns
        Next i  '    <-- I want the loop to go to the next rows (from the bottom most row going -3, -2, -1, 0) and scoot over 10 rows to paste.
    
        ThisWorkbook.Sheets("SampleResult").Select
        'Store results in "USRMResultsAnalysis" Sheet from "SampleResult" Sheet
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12

    Thank you for this foundation! Peeping through it now!

    Thank you Paul for this foundation. I am combing through it right now. I ran into a few kinks so far, but I think I can iron them out. The alphabet, I can handle. But putting this together has been . I do have a question though, when you are equating copy and pastes, does it go:

    Source = Destination or Destination = Source

    It looks like the latter is true. Nonetheless, this is truly a tremendous help! I am over the moon and will be back shortly.


    Quote Originally Posted by Paul_Hossler View Post
    This is just a concept to suggest a possible approach


    It finds the last cell in Col A with data and uses that and 3 rows above in a 0-3 loop

    In the 0-3 loop, it move data to (for first line) to (2,3), (2,13), (2,23), and (2,33) using iOffset = iOffset + 10


    Option Explicit
    Sub Concept()
        Dim strPath As String
        Dim strfolderpath As String
        Dim LastRow As Long
        
        Dim iStartRowOfLastBlock As Long, iBlockRow As Long
        Dim shtData As Worksheet, shtResult As Worksheet
        Dim iOffset As Long
        
        Set shtData = Sheets("Data")
        Set shtResult = Sheets("SampleResult")
        iStartRowOfLastBlock = shtData.Cells(Rows.Count, 1).End(xlUp).Offset(-3, 0).Row
    
        iOffset = 0
        For iBlockRow = 0 To 3      '    <- 4 loops, 1 for each line
            iStartRowOfLastBlock = iStartRowOfLastBlock + iBlockRow
            'didn't understand this
            'This is the beginning of the loop
            'Offset = 12 <-- I think it is 12, I highlighted it from the first entry (C2) to the next symmetrical entry (R1) it says 12 on "SampleResult"
    
            'ii = sht1.Range("A:A").Find("*", searchdirection:=xlDown).Offset(-3, 0).Row
            'sht2.Columns(1, Offset).PasteSpecial Paste:=xlPasteValuesAndNumberFormats <-- Error here so far
            'Flask 1
            With shtResult  '   I think I know my ABCs but better check
                .Cells(2, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 1).Value
                .Cells(4, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 2).Value
                .Cells(5, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 3).Value
                .Cells(1, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 4).Value
                .Cells(1, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 5).Value
                .Cells(3, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 6).Value
                .Cells(6, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 7).Value
                .Cells(7, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 8).Value
                .Cells(4, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 9).Value
                .Cells(4, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 10).Value
                .Cells(4, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 11).Value
                .Cells(12, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 12).Value
                .Cells(13, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 13).Value
                .Cells(14, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 14).Value
                .Cells(12, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 15).Value
                .Cells(13, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 16).Value
                .Cells(14, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 17).Value
                .Cells(20, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 18).Value
                .Cells(20, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 19).Value
                .Cells(21, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
                .Cells(21, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
                .Cells(20, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 21).Value
                .Cells(22, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 22).Value
                .Cells(20, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 23).Value
                .Cells(20, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 24).Value
                .Cells(21, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
                .Cells(21, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
                .Cells(20, 6 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 26).Value
                .Cells(22, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 27).Value
                .Cells(5, 11 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 28).Value
            End With
            
            iOffset = iOffset + 10
        '---------------------------------------------------------------------------------------------------------------------ROWS or COLUMNS -- I did columns
        Next i  '    <-- I want the loop to go to the next rows (from the bottom most row going -3, -2, -1, 0) and scoot over 10 rows to paste.
    
        ThisWorkbook.Sheets("SampleResult").Select
        'Store results in "USRMResultsAnalysis" Sheet from "SampleResult" Sheet
    End Sub

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I do have a question though, when you are equating copy and pastes, does it go:

    Source = Destination or Destination = Source

    It looks like the latter is true. Nonetheless, this is truly a tremendous help! I am over the moon and will be back shortly.
    Not sure what you mean by 'equating'

    The syntax for Copy/Paste is something like

    ws1.Range("A1").Copy ws2.Range("C3")

    where you're copying A1 and putting it into C3
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    Sorry for the delay, I had to make an 8 hour drive yesterday. Nonetheless, Yes, that is what I meant, like which of copy or paste goes on the left and then to the right; but you answered it. Thanks.

    I did try the code and I saw two responses.
    1) Copy/Paste from "Data" to "Sample Results" was off after the first row. So I tried to change the offset value from 10 to 12 to 15 and it didn't work. I'm not sure if I'm doing that correctly.

    I used what you gave me, triple checked the cell coordinates, counted the cells between each new paste and made sure it matched the offset value (of 15), and it hasn't worked yet. When choosing the offset value, if my first data is in C1, then with an offset of 15, the next data should show up at R1?

    2) My last posted question was to help me try and set up the 2nd copy/paste that was needed to go to the "USRMResultsAnalysis" from "SampleResult". But I can still work on that, I just can't see if it is working since the middle man "SampleResult" isn't quite write yet.

  15. #15
    I tried the code again, and now it is copying rows 1, 2, and 4. I don't know what happened to row 3.

    This is the code:

    Dim strPath As String
    Dim strfolderpath As String
    Dim LastRow As Long

    Dim iStartRowOfLastBlock As Long, iBlockRow As Long, iStartRowOfNextToLastBlock As Long
    Dim shtData As Worksheet, shtResult As Worksheet, shtAnalysis As Worksheet
    Dim iOffset As Long

    Set shtData = Sheets("Data")
    Set shtResult = Sheets("SampleResult")
    Set shtAnalysis = Sheets("USRMResultsAnalysis")
    iStartRowOfLastBlock = shtData.Cells(Rows.count, 1).End(xlUp).Offset(-3, 0).row



    iOffset = 0
    For iBlockRow = 0 To 3 ' <- 4 loops, 1 for each line
    iStartRowOfLastBlock = iStartRowOfLastBlock + iBlockRow


    'This is the beginning of the loop



    'Flask 1
    With shtResult ' I think I know my ABCs but better check
    .Cells(2, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 1).Value
    .Cells(4, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 2).Value
    .Cells(5, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 3).Value
    .Cells(1, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 4).Value
    .Cells(1, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 5).Value
    .Cells(3, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 6).Value
    .Cells(6, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 7).Value
    .Cells(7, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 8).Value
    .Cells(4, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 9).Value
    .Cells(4, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 10).Value
    .Cells(4, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 11).Value
    .Cells(12, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 12).Value
    .Cells(13, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 13).Value
    .Cells(14, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 14).Value
    .Cells(12, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 15).Value
    .Cells(13, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 16).Value
    .Cells(14, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 17).Value
    .Cells(20, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 18).Value
    .Cells(20, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 19).Value
    .Cells(21, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
    .Cells(21, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
    .Cells(20, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 21).Value
    .Cells(22, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 22).Value
    .Cells(20, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 23).Value
    .Cells(20, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 24).Value
    .Cells(21, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
    .Cells(21, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
    .Cells(20, 6 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 26).Value
    .Cells(22, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 27).Value
    .Cells(5, 11 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 28).Value
    End With

    iOffset = iOffset + 15
    '---------------------------------------------------------------------------------------------------------------------ROWS or COLUMNS -- I did columns
    Next iBlockRow ' <-- I want the loop to go to the next rows (from the bottom most row going -3, -2, -1, 0) and scoot over 10 rows to paste.


    ThisWorkbook.Sheets("SampleResult").Activate

    End With
    End With
    End With
    End If

    'This part goes from shtResult to shtAnalysis which has the same layout as shtData, so I figured ishould be able to flip the previous code and apply it.


    iOffset = 0
    For iBlockRow = 0 To 3 ' <- 4 loops, 1 for each line
    iStartRowOfLastBlock = iStartRowOfLastBlock + iBlockRow
    iStartRowOfLastBlock = shtAnalysis.Cells(Rows.count, 1).End(xlUp).Offset(3, 0).row
    With shtAnalysis
    shtResult.Cells(iStartRowOfLastBlock, 1).Value = .Cells(2, 3 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 2).Value = .Cells(4, 3 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 3).Value = .Cells(5, 3 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 4).Value = .Cells(1, 8 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 5).Value = .Cells(1, 3 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 6).Value = .Cells(3, 3 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 7).Value = .Cells(6, 3 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 8).Value = .Cells(7, 3 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 9).Value = .Cells(4, 7 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 10).Value = .Cells(7, 8 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 11).Value = .Cells(31, 6 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 12).Value = .Cells(31, 7 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 13).Value = .Cells(31, 8 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 14).Value = .Cells(32, 6 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 15).Value = .Cells(32, 7 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 16).Value = .Cells(32, 8 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 17).Value = .Cells(41, 11 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 18).Value = .Cells(42, 11 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 19).Value = .Cells(6, 11 + iOffset).Value
    shtResult.Cells(iStartRowOfLastBlock, 9).Value = .Cells(4, 7 + iOffset).Value
    End With

    iOffset = iOffset + 15


    iStartRowOfLastBlock = shtAnalysis.Cells(Rows.count, 1).End(xlUp).Offset(3, 0).row

    Next iBlockRow




    Call DataGen.PDFActiveSheetNoPromptCheck

    Application.ScreenUpdating = False




    'NEXT ONE


    StarchAnalysisWkst.TextBox3 = "Run -" & NextNumber(Worksheets("Data").Range("A3:A65536")) - 3



    Me.txtFile4.Text = "CISM Starch Result - " + Format(Date, "yy-mmm-dd-w") + "-" + TextBox3.Value + "-D"
    Me.txtFile3.Text = "CISM Starch Result - " + Format(Date, "yy-mmm-dd-w") + "-" + TextBox3.Value + "-C"
    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






    On Error Resume Next




    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •