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

 

[Peter_SSs]

Try this instead (in ThisWorkbook module)

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

 

[abdo meghari]

perfect!
Can I implement for specific sheets instead of each sheet ?
I try to do that for first and second sheets by using loop but doesn't work.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  On Error Resume Next
  Dim i as long 
  For i = 1 To 2
  Sh(i).Name = "INVOICE " & Sh.Range("F" & Rows.Count).End(xlUp).Text
  Next
End Sub

 

[Peter_SSs]

Can I implement for specific sheets instead of each sheet ?

Yes, but if the following is not what you want or you can't adapt it then we would need details of how to determine which sheets to do this for or which sheet to not do it for.

This will act on the the left-most two sheets in the workbook only

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

 

[abdo meghari]

great !
just curiosity if I put specific sheets in array supposes rename
like this

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

how can I do that correctly.

 

[Peter_SSs]

I'm not really sure about how that would work. We could certainly do it by sheet name but that would only work once. If the last value in column F might change, the worksheet name would not update. On the other hand if this is to be a one-off worksheet name change then perhaps the Workbook_SheetActivate is not the best 'event' to use.

Perhaps you could explain in more detail about how the sheet names should change and if the name should change again if the last value in column F changes.
Why were you trying to use for the worksheet activate event?

 

[abdo meghari]

if the name should change again if the last value in column F changes.

Yes

Why were you trying to use for the worksheet activate event?

ok if you see there is another way to do that I don't mind

Why were you trying to use for the worksheet activate event?

it's not important , I don't have to use worksheet activate event.

Perhaps you could explain in more detail about how the sheet names should change and if the name should change again if the last value in column F changes.

when change the amount in lastrow for column F for any sheet or sheets together then should change at once for sheets

 

[Peter_SSs]

when change the amount in lastrow for column F for any sheet or sheets together then should change at once for sheets

Then post #2 remains the solution?
Yet in post #3 you seemed to be asking about specific sheets, not for any sheet.
I am confused.

 

[abdo meghari]

Yet in post #3 you seemed to be asking about specific sheets, not for any sheet.
I am confused.

I meant when I have sheets(SH1,IN1)
one of them change in last row then will change for sheets sh1,in1 again
and if change in last row for both sheets then will change for sheets sh1,in1 again.
example:

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
23	2	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
24	TOTAL		22.00		49,550.00
IN1

Cell Formulas
Range	Formula
D24,F24	D24	=SUM(D22:D23)
F22:F23	F22	=D22*E22

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	5.00	500.00	2,500.00
23	2	VEGA 55A R KOR	4.00	425.00	1,700.00
24	3	LARGEST 60A L HIGH JAP	4.00	440.00	1,760.00
25	4	KM 215/65R16 TA31 KOR	5.00	460.00	2,300.00
26	5	265/70R16 ALGERIA	8.00	630.00	5,040.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	8.00	880.00	7,040.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	15	KM 205/65R16 HS63 KOR	4.00	450.00	1,800.00
36	16	GC 385/65R22.5 AT131 CHI	12.00	1,640.00	19,680.00
37	18	KM 235/65R17 HP71 KOR	1.00	875.00	875.00
38	19	KM 235/55R19 PS71 KOR	2.00	670.00	1,340.00
39	20	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
40	21	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
41	TOTAL		94.00		110,460.00
SH1

Cell Formulas
Range	Formula
D41,F41	D41	=SUM(D22:D40)
F22:F38	F22	=E22*D22
F39:F40	F39	=D39*E39

result

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
23	2	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
24	TOTAL		22.00		49,550.00
INVOICE 49,550.00

Cell Formulas
Range	Formula
D24,F24	D24	=SUM(D22:D23)
F22:F23	F22	=D22*E22

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	5.00	500.00	2,500.00
23	2	VEGA 55A R KOR	4.00	425.00	1,700.00
24	3	LARGEST 60A L HIGH JAP	4.00	440.00	1,760.00
25	4	KM 215/65R16 TA31 KOR	5.00	460.00	2,300.00
26	5	265/70R16 ALGERIA	8.00	630.00	5,040.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	8.00	880.00	7,040.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	15	KM 205/65R16 HS63 KOR	4.00	450.00	1,800.00
36	16	GC 385/65R22.5 AT131 CHI	12.00	1,640.00	19,680.00
37	18	KM 235/65R17 HP71 KOR	1.00	875.00	875.00
38	19	KM 235/55R19 PS71 KOR	2.00	670.00	1,340.00
39	20	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
40	21	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
41	TOTAL		94.00		110,460.00
INVOICE NO 110,460.00

Cell Formulas
Range	Formula
D41,F41	D41	=SUM(D22:D40)
F22:F38	F22	=E22*D22
F39:F40	F39	=D39*E39

change in IN1 sheet.

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
23	2	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
24	3	KM 14R22.5 MA11 KOR	12.00	2,000.00	24,000.00
25	TOTAL		22.00		73,550.00
INVOICE 49,550.00

Cell Formulas
Range	Formula
D25	D25	=SUM(D22:D23)
F22:F24	F22	=D22*E22
F25	F25	=SUM(F22:F24)

result for both sheets

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
23	2	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
24	3	KM 14R22.5 MA11 KOR	12.00	2,000.00	24,000.00
25	TOTAL		22.00		73,550.00
INVOICE 73,550.00

Cell Formulas
Range	Formula
D25	D25	=SUM(D22:D23)
F22:F24	F22	=D22*E22
F25	F25	=SUM(F22:F24)

keep second sheet without change.

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	5.00	500.00	2,500.00
23	2	VEGA 55A R KOR	4.00	425.00	1,700.00
24	3	LARGEST 60A L HIGH JAP	4.00	440.00	1,760.00
25	4	KM 215/65R16 TA31 KOR	5.00	460.00	2,300.00
26	5	265/70R16 ALGERIA	8.00	630.00	5,040.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	8.00	880.00	7,040.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	15	KM 205/65R16 HS63 KOR	4.00	450.00	1,800.00
36	16	GC 385/65R22.5 AT131 CHI	12.00	1,640.00	19,680.00
37	18	KM 235/65R17 HP71 KOR	1.00	875.00	875.00
38	19	KM 235/55R19 PS71 KOR	2.00	670.00	1,340.00
39	20	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
40	21	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
41	TOTAL		94.00		110,460.00
INVOICE NO 110,460.00

Cell Formulas
Range	Formula
D41,F41	D41	=SUM(D22:D40)
F22:F38	F22	=E22*D22
F39:F40	F39	=D39*E39

 

[Peter_SSs]

I still do not understand.

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

If not, why is it marked as the solution?
and
In what way does it not work for you?