Code revision for transferring/clearing data between worksheets

[Lil Stinker]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
23		SERIAL NUMBERS			Main											Secondary		Misc				db_row
24		202	850	1000	1001	1200	1250	1300	1500	1600	1700	1800	1900	2000	2100	123	444					24
25		2200	2300	2330	2400	2500	2600	2700	2800	2900	3040	3200	3300	3400	3444	451	500					25
26		3500	3505	3600	3700	3800	3900	4000	4100	4200	4300	4400	4500	4600	4650	500	508					26
27		4700	4800	4900	5000	5002	5003	5005	5400	5500	5600	6546	6549	7004	8004	650	784					27
28		9640														960						28
29
30
31
32
33
34
35
36
37
38														15001		8500	1542	150	250
RA Receipt

Sheet2 ("Serial Log") is where the numbers are backed up to in the same order.

2-24 mrexcelTEST.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	WR	RA No.	Page No.	sr1	sr2	sr3	sr4	sr5	sr6	sr7	sr8	sr9	sr10	sr11	sr12	sr13	sr14	sxb1	sxb2	pws1	pws2	RArow	Row
24	WR	4	1	202	850	1000	1001	1200	1250	1300	1500	1600	1700	1800	1900	2000	2100	123	444			24	24
25	WR	4	1	2200	2300	2330	2400	2500	2600	2700	2800	2900	3040	3200	3300	3400	3444	451	500			25	25
26	WR	4	1	3500	3505	3600	3700	3800	3900	4000	4100	4200	4300	4400	4500	4600	4650	500	508			26	26
27	WR	4	1	4700	4800	4900	5000	5002	5003	5005	5400	5500	5600	6546	6549	7004	8004	650	784			27	27
28	WR	4	1	9640														960				28	28
Serial Log

Cell Formulas
Range	Formula
W24:W28	W24	=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).

    '''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.

 

[mikenelena]

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.

' 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.

 

[Lil Stinker]

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.

 

[mikenelena]

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

 

[Lil Stinker]

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:

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):

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:

        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.