Code revision for transferring/clearing data between worksheets

Lil Stinker

Board Regular
Joined
Feb 16, 2022
Messages
138
Office Version
  1. 2019
Platform
  1. Windows
Hey folks,
I'm looking for a code revision to fix two issues I have with the current form and how it transfers data back and forth between two worksheets. The setup is a bit complicated.

Setup:
On Sheet1 ("RA Receipt"), serial numbers are scanned into three different ranges depending on the type of equipment: Main, Secondary and Misc.
2-24 mrexcelTEST.xlsm
ABCDEFGHIJKLMNOPQRSTUV
23SERIAL NUMBERSMainSecondaryMiscdb_row
2420285010001001120012501300150016001700180019002000210012344424
252200230023302400250026002700280029003040320033003400344445150025
263500350536003700380039004000410042004300440045004600465050050826
274700480049005000500250035005540055005600654665497004800465078427
28964096028
29
30
31
32
33
34
35
36
37
381500185001542150250
RA Receipt


Sheet2 ("Serial Log") is where the numbers are backed up to in the same order.
2-24 mrexcelTEST.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
2WRRA No.Page No.sr1sr2sr3sr4sr5sr6sr7sr8sr9sr10sr11sr12sr13sr14sxb1sxb2pws1pws2RArowRow
24WR412028501000100112001250130015001600170018001900200021001234442424
25WR41220023002330240025002600270028002900304032003300340034444515002525
26WR41350035053600370038003900400041004200430044004500460046505005082626
27WR41470048004900500050025003500554005500560065466549700480046507842727
28WR4196409602828
Serial Log
Cell Formulas
RangeFormula
W24:W28W24=ROW()


Here is the code used to transfer the data row by row. All of the variables are Dimmed As Long. (This is just the relevant segment of a much larger macro that transfers different data sets to other worksheets from the same form on the RA Receipt).
VBA Code:
    '''add / update ra serials
    lastITEMROW = .Range("B39").End(xlUp).Row 'last serial row
        If lastITEMROW < 24 Then GoTo NoSerials
            For itemROW = 24 To lastITEMROW
                If .Range("V" & itemROW).Value = Empty Then 'new ra
                    raITEMROW = RAseriallog.Range("A1048576").End(xlUp).Row + 1  'first avail row
                    RAseriallog.Range("A" & raITEMROW).Value = .Range("P4").Value 'ra prefix
                    RAseriallog.Range("B" & raITEMROW).Value = .Range("AD6").Value 'add ra#
                    RAseriallog.Range("C" & raITEMROW).Value = .Range("AB9").Value 'add page #
                    RAseriallog.Range("V" & raITEMROW).Value = itemROW 'ra item row
                    RAseriallog.Range("W" & raITEMROW).Value = "=Row()" 'add db_row
                    .Range("V" & itemROW).Value = raITEMROW 'add db_row to ra
                Else 'existing ra item
                    raITEMROW = .Range("V" & itemROW).Value 'existing item row
                End If
            RAseriallog.Range("D" & raITEMROW & ":U" & raITEMROW).Value = Range("B" & itemROW & ":S" & itemROW).Value  'backup serials
            Next itemROW
NoSerials:

Issues:
1. Because this copies data row by row, it only references B39 to establish the last item row. The problem is, if I only enter numbers into the Secondary range or the Misc range, it doesn't transfer that data over because column B is empty. How do I revise the code to recognize not just Col B and the last item row, but Col P and the last row as well as Col R and the last row?

2. I would also like to know how I can clear the entries on the Serial Log if they are deleted on the RA Receipt sheet. Currently, it only works to overwrite the data on the Serial Log. It assigns the corresponding row # to both sheets so the data matches however, if I delete any numbers from the last row on the RA Receipt and refresh the log, the Serial Log still has those numbers from the last row stored. I came up with a way to clear the entire row of the Serial Log but that is problematic because if the numbers stored in the Secondary or Misc ranges exceed the numbers stored in the Main range, clearing the entire row obviously wipes everything out regardless of the range it is in.

I feel like a cell by cell approach is a better way to go but I didn't author this code and I don't know enough about VBA to change it appropriately. I'd appreciate any help you can offer.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi Lil,

Let me take a stab at Issue 1. To include additional columns in the code and use their last row as the last item row, you need to modify the code to find the maximum row number among the columns you want to include. Here's one way you can do that.

VBA Code:
' find the last row for each column
lastITEMROW_B = .Range("B39").End(xlUp).Row
lastITEMROW_P = .Range("P" & Rows.Count).End(xlUp).Row
lastITEMROW_R = .Range("R" & Rows.Count).End(xlUp).Row

' determine the last item row
lastITEMROW = WorksheetFunction.Max(lastITEMROW_B, lastITEMROW_P, lastITEMROW_R)

' check if there are any serials
If lastITEMROW < 24 Then GoTo NoSerials

' loop through the rows and update the RA serials
For itemROW = 24 To lastITEMROW
    If .Range("V" & itemROW).Value = Empty Then 'new ra
        raITEMROW = RAseriallog.Range("A1048576").End(xlUp).Row + 1  'first avail row
        RAseriallog.Range("A" & raITEMROW).Value = .Range("P4").Value 'ra prefix
        RAseriallog.Range("B" & raITEMROW).Value = .Range("AD6").Value 'add ra#
        RAseriallog.Range("C" & raITEMROW).Value = .Range("AB9").Value 'add page #
        RAseriallog.Range("V" & raITEMROW).Value = itemROW 'ra item row
        RAseriallog.Range("W" & raITEMROW).Value = "=Row()" 'add db_row
        .Range("V" & itemROW).Value = raITEMROW 'add db_row to ra
    Else 'existing ra item
        raITEMROW = .Range("V" & itemROW).Value 'existing item row
    End If
    RAseriallog.Range("D" & raITEMROW & ":U" & raITEMROW).Value = Range("B" & itemROW & ":S" & itemROW).Value  'backup serials
Next itemROW

NoSerials:

In this code, the lastITEMROW_B, lastITEMROW_P, and lastITEMROW_R variables find the last row for each column using the End(xlUp) method. The WorksheetFunction.Max function is used to find the maximum row number among the three columns, which is then used as the lastITEMROW variable. The rest of your code remains the same.
 
Upvote 0
Thanks, Mikenelena, this appears to work though I had to modify it a bit. I couldn't use the Rows.Count method because there is information starting in Row 39 that should not be transferred over to the other worksheet. Changing it to "B39", "P39" and "R39" for the lastITEMROW variables along with using the Max function seems to have done the trick.
 
Upvote 0
Thanks, Mikenelena, this appears to work though I had to modify it a bit. I couldn't use the Rows.Count method because there is information starting in Row 39 that should not be transferred over to the other worksheet. Changing it to "B39", "P39" and "R39" for the lastITEMROW variables along with using the Max function seems to have done the trick.
Great! I'm glad you got it working. As to your 2nd question, can you give me an example of what you might need in terms of clearing the entries on the serial log? What would be an example of a range of your RAsheet data, and the corresponding range on the serial log?

Do you have the line of code that is doing this:
It assigns the corresponding row # to both sheets so the data matches

Thanks,
Mike
 
Upvote 0
I have to revise that second issue a little based on this recent fix for the first issue. Between establishing the last row successfully for all three ranges on the RAreceipt along with a worksheet change event that automatically sorts the numbers entered into these ranges, most of the second issue has taken care of itself. The problem that remains is when clearing the last row in these ranges on the RAreceipt, how to also clear the same corresponding rows on the Serial Log.

For example, using the sample provided in the beginning, there are 57 Main serial numbers and 9 Secondary serials in rows 24-28. If I remove all of the serial numbers in rows 27 & 28 and save it to the Serial Log, now the serials are wiped out correctly when they weren't before today's fix. However, the rows and row numbers though empty, still remain on both the Serial Log & RAreceipt. I would like to clear the row numbers from Column V on the RAreceipt as well as delete the row entirely from the Serial Log.

Now, I have a way of doing this using a Selection Change Event that determines the row that is currently selected on RAreceipt and deletes that entire row from the Serial Log. However, it only happens when you manually select any cell within that range on RAreceipt. I would like for it to happen automatically when the user saves the serial numbers to the Serial Log so the empty rows are automatically deleted from the Log. Here is that code.

Based on the row you select on RAreceipt, the row number is added to cell AD7 the "Selected Row" on the same sheet:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'on existing serial no. line selection
If Not Intersect(Target, Range("B24:S38")) Is Nothing Then
    Range("AD7").Value = Target.Row
End If

End Sub

This sub then clears the data and deletes the row from the Serial Log (all the variables not shown here are also dimmed As Long):
VBA Code:
Sub clearLINE()
    Dim selROW As Long, resultitemDBROW As Long
    
    With RAreceipt
        If .Range("AD7").Value = Empty Then Exit Sub
        selROW = .Range("AD7").Value
        raITEMROW = .Range("V" & selROW).Value 'ra item db_row
        lastraITEMROW = RAseriallog.Range("AU10002").End(xlUp).Row 'last ra item row
            If raITEMROW = 0 Or lastraITEMROW < 3 Then Exit Sub
            For resultROW = 3 To lastraITEMROW
                raROW = RAseriallog.Range("AU" & resultROW).Value 'ra row
                If raROW > selROW Then 'update ra row for serials after selected row
                    lastitemRESULTROW = RAseriallog.Range("AV" & resultROW).Value
                    RAseriallog.Range("V" & lastitemRESULTROW).Value = RAseriallog.Range("V" & lastitemRESULTROW) - 1 'reduce ra row
                End If
            Next resultROW
        RAseriallog.Range(raITEMROW & ":" & raITEMROW).EntireRow.Delete
    End With

End Sub

To answer your question as to what code determines the rows and row numbers, it's these lines from the original code I posted:
VBA Code:
        RAseriallog.Range("V" & raITEMROW).Value = itemROW 'ra item row  (((this tells the Log the row # the serials are in on RAreceipt)))
        RAseriallog.Range("W" & raITEMROW).Value = "=Row()" 'add db_row   (((this just determines the row # on the Log where the numbers are being copied to)))
        .Range("V" & itemROW).Value = raITEMROW 'add db_row to ra   (((then the row # from the Serial Log is copied to Col V on RAreceipt)))

It's handy being able to select the row you want to delete however, in this case, it would be better for it to run automatically each time the numbers are recorded/saved.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top