Code that will copy range from one worksheet and pastes it to another... but with a 'twist'

[kbishop94]

I need my code to copy a range (which is a single column) from one worksheet, and then paste it to another worksheet, but when and if the number of rows that is being copied is more than 15 rows, it will paste row 16 in another column (so it cant go below row 16)... and then if its still longer than 30 rows, then it then will paste the rest of the rows in yet another column (column H)... sounds strange, but i think its simpler for me just to show you (as usual) what I am getting at with a couple of pictures.

This is worksheet "ANALYTICS" which is the sheet that I have my code temporarily place data that is going to be used somewhere else, or saved for future calculations.

In this picture I am showing you how my current code takes the rows of data in column E and reduces them down (elminating the empty rows) and copies and pastes them in column F. Next (the code that I am requesting help with ) it needs to take the F column data and copies it over to the worksheet "REPORTS" (next picture...)

Here is the reports sheet before the data is copied. The data needs to be pasted starting at A2, BUT... only can go down to row 16. If there happens to be more data then that number of rows, then it needs to take the remainder of rows and paste them starting at D2. If there is still yet more rows that didnt get pasted between columns A and D, then I need that remainder to then be pasted starting at H2. Make sense??

And here is what it should look like and what I need the code to produce using my test demonstration data in the previous pictures. What was in column F in the first picture and on the worksheet "ANALYTICS", is now pasted onto this worksheet ("REPORTS") starting at A2 and goes down just to A16, and then pastes the remainder of the rows starting at D2. (and if it was longer than D16, then the remainder of that would have gone to H2 to pasted.)

Is what I'm asking even possible? Thanks for any help with this code, or with offering any other suggestions of accomplishing this but using a different method.

 

[kbishop94]

Re: help with code that will copy range from one worksheet and pastes it to another... but with a 'twist'

Here is my current existing code that tallies the number of each of those variables (which in turn they actually came from what checkbox was previsouly ticked on a userform)

    qRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    bRow = ActiveSheet.UsedRange.Cells.Find("*", , , , xlByRows, xlPrevious).Row
    tCnt = ActiveSheet.Range(Cells(18, 2), Cells(bRow, 2)).SpecialCells(xlCellTypeVisible).Cells.Count

aRow = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Row

    For Each cP In Range(Cells(18, 16), Cells(aRow, 16)).SpecialCells(xlCellTypeVisible)
        If cP Like "*ALL*" Then aall = aall + 1
        If cP Like "*CSR0o*" Then csr0o = csr0o + 1
        If cP Like "*CSR1A*" Then csr1a = csr1a + 1
        If cP Like "*CSR2B*" Then csr2b = csr2b + 1
        If cP Like "*CSR3C*" Then csr3c = csr3c + 1
        If cP Like "*CSR4D*" Then csr4d = csr4d + 1
        If cP Like "*CSR5E*" Then csr5e = csr5e + 1
        If cP Like "*CSR6F*" Then csr6f = csr6f + 1
        If cP Like "*CSR7G*" Then csr7g = csr7g + 1
        If cP Like "*CSR8H*" Then csr8h = csr8h + 1
        If cP Like "*CSR9I*" Then csr9i = csr9i + 1
        If cP Like "*CSR10J*" Then csr10j = csr10j + 1
        If cP Like "*CSR11K*" Then csr11k = csr11k + 1
        If cP Like "*CSR12L*" Then csr12l = csr12l + 1
        If cP Like "*CSR13M*" Then csr13m = csr13m + 1
        If cP Like "*CSR14N*" Then csr14n = csr14n + 1
        If cP Like "*CSR15O*" Then csr15o = csr15o + 1
        If cP Like "*CSR16P*" Then csr16p = csr16p + 1
        If cP Like "*CSR17Q*" Then csr17q = csr17q + 1
        If cP Like "*CSR18R*" Then csr18r = csr18r + 1
        If cP Like "*CSR19S*" Then csr19s = csr19s + 1
        If cP Like "*CSR20T*" Then csr20t = csr20t + 1
        If cP Like "*CSR21U*" Then csr21u = csr21u + 1
        If cP Like "*CSR22V*" Then csr22v = csr22v + 1
        If cP Like "*CSR23W*" Then csr23w = csr23w + 1
        If cP Like "*CSR24X*" Then csr24x = csr24x + 1
        If cP Like "*SEA0o*" Then sea0o = sea0o + 1
        If cP Like "*SEA1A*" Then sea1a = sea1a + 1
        If cP Like "*SEA2B*" Then sea2b = sea2b + 1
        If cP Like "*SEA3C*" Then sea3c = sea3c + 1
        If cP Like "*SEA4D*" Then sea4d = sea4d + 1
        If cP Like "*SEA5E*" Then sea5e = sea5e + 1
        If cP Like "*SEA6F*" Then sea6f = sea6f + 1
        If cP Like "*SEA7G*" Then sea7g = sea7g + 1
        If cP Like "*SEA8H*" Then sea8h = sea8h + 1
        If cP Like "*SEA9I*" Then sea9i = sea9i + 1
        If cP Like "*SEA10J*" Then sea10j = sea10j + 1
        If cP Like "*SEA11K*" Then sea11k = sea11k + 1
        If cP Like "*SEA12L*" Then sea12l = sea12l + 1
        If cP Like "*SEA13M*" Then sea13m = sea13m + 1
        If cP Like "*SEA14N*" Then sea14n = sea14n + 1
        If cP Like "*MIS0o*" Then mis0o = mis0o + 1
        If cP Like "*MIS1A*" Then mis1a = mis1a + 1
        If cP Like "*MIS2B*" Then mis2b = mis2b + 1
        If cP Like "*MIS3C*" Then mis3c = mis3c + 1
        If cP Like "*MIS4D*" Then mis4d = mis4d + 1
        If cP Like "*MIS5E*" Then mis5e = mis5e + 1
        If cP Like "*MIS6F*" Then mis6f = mis6f + 1
        If cP Like "*MIS7G*" Then mis7g = mis7g + 1
        If cP Like "*MIS8H*" Then mis8h = mis8h + 1
        If cP Like "*MIS9I*" Then mis9i = mis9i + 1
        If cP Like "*MIS10J*" Then mis10j = mis10j + 1
        If cP Like "*EHS0o*" Then ehs0o = ehs0o + 1
        If cP Like "*EHS1A*" Then ehs1a = ehs1a + 1
        If cP Like "*EHS2B*" Then ehs2b = ehs2b + 1
        If cP Like "*EHS3C*" Then ehs3c = ehs3c + 1
        If cP Like "*EHS4D*" Then ehs4d = ehs4d + 1
        If cP Like "*EHS5E*" Then ehs5e = ehs5e + 1
        If cP Like "*EHS6F*" Then ehs6f = ehs6f + 1
        If cP Like "*EHS7G*" Then ehs7g = ehs7g + 1
        If cP Like "*EHS8H*" Then ehs8h = ehs8h + 1
        If cP Like "*EHS9I*" Then ehs9i = ehs9i + 1
        If cP Like "*EHS10J*" Then ehs10j = ehs10j + 1
        If cP Like "*EHS11K*" Then ehs11k = ehs11k + 1
    Next cP

    For Each cP In Range(Cells(18, 16), Cells(aRow, 16)).SpecialCells(xlCellTypeVisible)
        If cP Like "*ALL*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E1").Value = "Total Number of ALL INCIDENTS:  " & aall
        If cP Like "*CSR0o*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E2").Value = "Total Number of ALL CSR Incidents:  " & csr0o
        If cP Like "*CSR21U*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E3").Value = "Total Number of ALL LABELING CSR Incidents:  " & csr21u
        If cP Like "*CSR1A*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E4").Value = "Total Number of HAZARDOUS LABELING Incidents:  " & csr1a
        If cP Like "*CSR2B*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E5").Value = "Total Number of WRONG OR INCORRECT LABEL Incidents:  " & csr2b
        If cP Like "*CSR3C*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E6").Value = "Total Number of MISSING LABEL Incidents:  " & csr3c
        If cP Like "*CSR4D*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E7").Value = "Total Number of LABEL PLACEMENT Incidents:  " & csr4d
        If cP Like "*CSR22V*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E8").Value = "Total Number of ALL PACKAGING CSR Incidents:  " & csr22v
        If cP Like "*CSR5E*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E9").Value = "Total Number of LIDS OR CAPS RELATED Incidents:  " & csr5e
        If cP Like "*CSR6F*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E10").Value = "Total Number of INCORRECT PACKAGING Incidents:  " & csr6f
        If cP Like "*CSR7G*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E11").Value = "Total Number of PACKAGING FAILURE Incidents:  " & csr7g
        If cP Like "*CSR8H*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E12").Value = "Total Number of MIXED PRODUCTS IN PACKAGE Incidents:  " & csr8h
        If cP Like "*CSR23W*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E13").Value = "Total Number of ALL PRODUCT COND CSR Incidents:  " & csr23w
        If cP Like "*CSR9I*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E14").Value = "Total Number of PRODUCT IS LEAKING Incidents:  " & csr9i
        If cP Like "*CSR10J*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E15").Value = "Total Number of OFF OR WRONG COLOR Incidents:  " & csr10j
        If cP Like "*CSR11K*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E16").Value = "Total Number of PERFORMANCE ISSUE Incidents:  " & csr11k
        If cP Like "*CSR12L*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E17").Value = "Total Number of CONTAMINATION Incidents:  " & csr12l
        If cP Like "*CSR13M*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E18").Value = "Total Number of PRODUCT DOESN'T LOOK THE SAME Incidents:  " & csr13m
        If cP Like "*CSR24X*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E19").Value = "Total Number of ALL MISCELLANEOUS CSR Incidents:  " & csr24x
        If cP Like "*CSR14N*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E20").Value = "Total Number of BILLING AND/OR PAPERWORK Incidents:  " & csr14n
        If cP Like "*CSR15O*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E21").Value = "Total Number of MISSED SHIPMENT OR ARRIVED LATE Incidents:  " & csr15o
        If cP Like "*CSR16P*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E22").Value = "Total Number of SLOPPY WORKMANSHIP Incidents:  " & csr16p
        If cP Like "*CSR17Q*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E23").Value = "Total Number of CARRIER/SHIPPER Incidents:  " & csr17q
        If cP Like "*CSR18R*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E24").Value = "Total Number of RECEIVED INCORRECT QUANTITY Incidents:  " & csr18r
        If cP Like "*CSR19S*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E25").Value = "Total Number of MATERIAL STILL REMAINING IN TANKER Incidents:  " & csr19s
        If cP Like "*CSR20T*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E26").Value = "Total Number of WRONG PRODUCT RECEIVED Incidents:  " & csr20t
        If cP Like "*SEA0o*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E27").Value = "Total Number of ALL SEATEX INTERNAL Incidents:  " & sea0o
        If cP Like "*SEA1A*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E28").Value = "Total Number of ALL PROCESS FAILURES Incidents:  " & sea1a
        If cP Like "*SEA2B*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E29").Value = "Total Number of ALL PROCESS DEVIATIONS Incidents:  " & sea2b
        If cP Like "*SEA3C*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E30").Value = "Total Number of CONTAMINATION (internal) Incidents:  " & sea3c
        If cP Like "*SEA4D*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E31").Value = "Total Number of MECHANICAL EQUIPMENT/FAILURE Incidents:  " & sea4d
        If cP Like "*SEA5E*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E32").Value = "Total Number of DOCUMENT/RECORD/PAPERWORK Incidents:  " & sea5e
        If cP Like "*SEA6F*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E33").Value = "Total Number of CUSTOMER OWNED MATERIALS Incidents:  " & sea6f
        If cP Like "*SEA7G*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E34").Value = "Total Number of CUSTOMER OWNED (NOT MATERIALS) Incidents:  " & sea7g
        If cP Like "*SEA8H*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E35").Value = "Total Number of LEAKING HOSE/PIPE/FITTING Incidents:  " & sea8h
        If cP Like "*SEA9I*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E36").Value = "Total Number of WORKMANSHIP (internal) Incidents:  " & sea9i
        If cP Like "*SEA10J*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E37").Value = "Total Number of INVENTORY Incidents:  " & sea10j
        If cP Like "*SEA11K*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E38").Value = "Total Number of CARRIER / SHIPPING Incidents:  " & sea11k
        If cP Like "*SEA12L*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E39").Value = "Total Number of HAZARDOUS LABELING/PLACARDS(internal) Incidents:  " & sea12l
        If cP Like "*SEA13M*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E40").Value = "Total Number of PACKAGING RELATED FAILURES(internal) Incidents:  " & sea13m
        If cP Like "*SEA14N*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E41").Value = "Total Number of OTHER/NOT LISTED Incidents:  " & sea14n
        If cP Like "*MIS0o*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E42").Value = "Total Number of ALL MISCELLANEOUS Incidents:  " & mis0o
        If cP Like "*MIS1A*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E43").Value = "Total Number of SUPPLIER RELATED (misc) Incidents:  " & mis1a
        If cP Like "*MIS2B*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E44").Value = "Total Number of CONTRACTOR Incidents:  " & mis2b
        If cP Like "*MIS3C*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E45").Value = "Total Number of FINDING FROM AUDIT Incidents:  " & mis3c
        If cP Like "*MIS4D*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E46").Value = "Total Number of THEFT OF PROPERTY Incidents:  " & mis4d
        If cP Like "*MIS5E*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E47").Value = "Total Number of UNEXPECTED OR INTENDED ALARM Incidents:  " & mis5e
        If cP Like "*MIS6F*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E48").Value = "Total Number of RAILCAR OR RAILCAR AREA Incidents:  " & mis6f
        If cP Like "*MIS7G*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E49").Value = "Total Number of MONTHLY DAMAGE - ROSENBERG Incidents:  " & mis7g
        If cP Like "*MIS8H*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E50").Value = "Total Number of MONTHLY DAMAGE - EL CAMPO Incidents:  " & mis8h
        If cP Like "*MIS9I*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E51").Value = "Total Number of PERSONNEL ISSUE/EMPLOYEE CONFLICT Incidents:  " & mis9i
        If cP Like "*MIS10J*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E52").Value = "Total Number of OTHER Incidents:  " & mis10j
        If cP Like "*EHS0o*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E53").Value = "Total Number of ALL EHS Incidents:  " & ehs0o
        If cP Like "*EHS1A*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E54").Value = "Total Number of INJURY (ehs) Incidents:  " & ehs1a
        If cP Like "*EHS2B*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E55").Value = "Total Number of NEAR MISS (ehs) Incidents:  " & ehs2b
        If cP Like "*EHS3C*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E56").Value = "Total Number of SLIP, TRIP, FALL (ehs) Incidents:  " & ehs3c
        If cP Like "*EHS4D*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E57").Value = "Total Number of STRUCK BY OBJECT (ehs) Incidents:  " & ehs4d
        If cP Like "*EHS5E*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E58").Value = "Total Number of EXPOSURE (ehs) Incidents:  " & ehs5e
        If cP Like "*EHS6F*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E59").Value = "Total Number of SPLASHED W CHEMICAL (ehs) Incidents:  " & ehs6f
        If cP Like "*EHS7G*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E60").Value = "Total Number of RESPIRATOR (ehs) Incidents:  " & ehs7g
        If cP Like "*EHS8H*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E61").Value = "Total Number of MOBILE INCIDENT(FORK-LIFT) (ehs) Incidents:  " & ehs8h
        If cP Like "*EHS9I*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E62").Value = "Total Number of GAS EXCURSION (ehs) Incidents:  " & ehs9i
        If cP Like "*EHS10J*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E63").Value = "Total Number of OTHER (ehs) Incidents:  " & ehs10j
        If cP Like "*EHS11K*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E64").Value = "Total Number of SPILLS (ehs) Incidents:  " & ehs11k
        If cP Like "*EHS12L*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E65").Value = "Total Number of PPE (ehs) Incidents:  " & ehs12l
        If cP Like "*EHS13M*" Then ActiveWorkbook.Worksheets("ANALYTICS").Range("E66").Value = "Total Number of ??? (ehs) Incidents:  " & ehs13m

    Next cP

And then here is the code that copies all of the rows that were just added to the analytics worksheet in the above code and removes all the blank rows and copies them and pastes them to the column right beside it on the same worksheet (so it copies from column E and pastes it to column F... which is what you see in my first picture I posted in my original post above.)

ActiveWorkbook.Worksheets("ANALYTICS").Activate
ActiveWorkbook.Worksheets("ANALYTICS").Range("E1:E70").SpecialCells(xlCellTypeConstants).Select
Selection.Copy
ActiveWorkbook.Worksheets("ANALYTICS").Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False