rename sheet based on number format in last row for each sheet

[abdo meghari]

hello,
I would rename sheet based on amount is existed in last row for column F when the sheet is active.
I try with this

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim lr As Long
lr = Range("F" & Rows.Count).End(xlUp).Row
ActiveSheet.Name = Format("INVOICE" & " " & lr, "#,##0.00")
End Sub

data before

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	DONGA 66A L KOR	2.00	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
33	12	DROUB 90A L KOR	1.00	575.00	575.00
34	13	KM 13R22.5 MA11 KOR	2.00	2,550.00	5,100.00
35	TOTAL		39.00		28,485.00
SH1

Cell Formulas
Range	Formula
D35,F35	D35	=SUM(D22:D34)
F22:F34	F22	=E22*D22

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	1.00	500.00	500.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	1.00	460.00	460.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	450.00	1,800.00
28	7	GC 385/65R22.5 AT131 CHI	12.00	1,640.00	19,680.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	KM 235/65R17 HP71 KOR	1.00	875.00	875.00
31	10	KM 235/55R19 PS71 KOR	2.00	670.00	1,340.00
32	TOTAL		33.00		32,425.00
IN1

Cell Formulas
Range	Formula
D32,F32	D32	=SUM(D22:D31)
F22:F31	F22	=E22*D22

what I want

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	DONGA 66A L KOR	2.00	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
33	12	DROUB 90A L KOR	1.00	575.00	575.00
34	13	KM 13R22.5 MA11 KOR	2.00	2,550.00	5,100.00
35	TOTAL		39.00		28,485.00
INVOICE 28,485.00

Cell Formulas
Range	Formula
D35,F35	D35	=SUM(D22:D34)
F22:F34	F22	=E22*D22

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	1.00	500.00	500.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	1.00	460.00	460.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	450.00	1,800.00
28	7	GC 385/65R22.5 AT131 CHI	12.00	1,640.00	19,680.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	KM 235/65R17 HP71 KOR	1.00	875.00	875.00
31	10	KM 235/55R19 PS71 KOR	2.00	670.00	1,340.00
32	TOTAL		33.00		32,425.00
INVOICE 32,425.00

Cell Formulas
Range	Formula
D32,F32	D32	=SUM(D22:D31)
F22:F31	F22	=E22*D22

 

[abdo meghari]

You have marked post #2 as the solution.
Does it solve your problem?

that's because my requirement in OP.
just I said

just curiosity if I put specific sheets in array supposes rename

I see you interest for this case that's why I gave you some examples.
this case could face in the future, that's it.
thank you for your solution.

 

[Peter_SSs]

Well, the problem is if you specify the sheet name 'SH1' for example, and then the code changes its name to, say, 'INVOICE 23,000.00' and then the last value in column F changes again, nothing will happen because now sheet 'SH1' no longer exists because it is now called 'INVOICE 23,000.00'

 

[abdo meghari]

because now sheet 'SH1' no longer exists because it is now called 'INVOICE 23,000.00'

OMG !

this is missed me, sorry !
but what if set line in the beginning code by search for SH1,IN1 if there is no existed then will rename to SH1,IN1 before search for last row and change name to last row ,could be possible?

 

[Peter_SSs]

but what if set line in the beginning code by search for SH1,IN1 if there is no existed then will rename to SH1,IN1 before search for last row and change name to last row ,could be possible?

Sorry, that makes no sense at all to me.

 

[abdo meghari]

Sorry, that makes no sense at all to me.

ok just I would share idea maybe useful ,sorry !
but I think instead of use sheets names in array could use sheets numbers in location?
I mean instead of write sheets(IN1,SH1) use sheets(1,3)
the IN1 sheet will be in first sheet in location, SH1 will be in third sheet in location
then will change based on location sheet instead of use specific sheets names.

 

[Peter_SSs]

but I think instead of use sheets names in array could use sheets numbers in location?
I mean instead of write sheets(IN1,SH1) use sheets(1,3)

That is basically what I did in post #4. The slight change for sheets 1 and 3 instead of 1 and 2 could be

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If Sh.Index = 1 Or Sh.Index = 3 Then
    On Error Resume Next
    Sh.Name = "INVOICE " & Sh.Range("F" & Rows.Count).End(xlUp).Text
  End If
End Sub

or another way, particularly if there was a lot of sheets to act on, would be

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Select Case Sh.Index
    Case 1, 3   '<- Could add more here if needed
      On Error Resume Next
      Sh.Name = "INVOICE " & Sh.Range("F" & Rows.Count).End(xlUp).Text
  End Select
End Sub

 

[abdo meghari]

yes this is really impressive .
many thanks for everything today.

 

[Peter_SSs]