ActiveWorkbook sheet name?

[samilynn]

How do I change this VBA code to work on the active sheet, regardless of the Sheet name?

With ActiveWorkbook.Worksheets("SHEET1").

Thank you!

Samantha

 

[HighAndWilder]

How do I change this VBA code to work on the active sheet, regardless of the Sheet name?

With ActiveWorkbook.Worksheets("SHEET1").

Thank you!

Samantha

With Activesheet

 

[Joe4]

How do I change this VBA code to work on the active sheet, regardless of the Sheet name?

With ActiveWorkbook.Worksheets("SHEET1").

Thank you!

Samantha

You usually don't really need to.
If you leave off the sheet reference, it defaults to running on whatever is the active sheet at the time.

 

[samilynn]

With Activesheet

Thank you!

 

[samilynn]

You usually don't really need to.
If you leave off the sheet reference, it defaults to running on whatever is the active sheet at the time.

Thank you!

 

[Joe4]

The only time you would really need it is if you were bouncing around between multiple sheets.

If you wanted to capture it in your VBA code so you can return to that sheet later in your code, you could do that like this with a Worksheet object:

Dim ws as Worksheet
Set ws = ActiveSheet

You obviously want to put that near the top of your code, before you move off of the sheet.
Then, you can return to that sheet later, like this:

ws.Activate

or if you want to reference a range on that sheet from wherever you are, you can do something like:

ws.Range("A1")...

If you simply wanted to capture the name of the ActiveSheet in a variable, you could do something like:

Dim wsName as String
wsName = ActiveSheet.Name

 

[HighAndWilder]

The only time you would really need it is if you were bouncing around between multiple sheets.

If you wanted to capture it in your VBA code so you can return to that sheet later in your code, you could do that like this with a Worksheet object:

Dim ws as Worksheet
Set ws = ActiveSheet

You obviously want to put that near the top of your code, before you move off of the sheet.
Then, you can return to that sheet later, like this:

ws.Activate

or if you want to reference a range on that sheet from wherever you are, you can do something like:

ws.Range("A1")...

If you simply wanted to capture the name of the ActiveSheet in a variable, you could do something like:

Dim wsName as String
wsName = ActiveSheet.Name

I always prefer setting a worksheet object but there are many ways to do it.

 

[samilynn]

The only time you would really need it is if you were bouncing around between multiple sheets.

If you wanted to capture it in your VBA code so you can return to that sheet later in your code, you could do that like this with a Worksheet object:

Dim ws as Worksheet
Set ws = ActiveSheet

You obviously want to put that near the top of your code, before you move off of the sheet.
Then, you can return to that sheet later, like this:

ws.Activate

or if you want to reference a range on that sheet from wherever you are, you can do something like:

ws.Range("A1")...

If you simply wanted to capture the name of the ActiveSheet in a variable, you could do something like:

Dim wsName as String
wsName = ActiveSheet.Name

Thank you again Joe, your training is very much appreciated!!!

One more question if it's ok:

I have a sheet with salereps which are assigned to two teams. How do I replace my simple formula (in rows 20 and 21) with some sort of a lookup, which basically says "lookup the rep names in a table, and total their sales as a team?" (the teams are on a different file, but I put them on the same sheet in this example for simplicity):

Book1.xlsx
	A	B	C	D	E
1	SLSREP	JAN SALES	FEB SALES	MAR SALES	APR SALES
2	REP1	$463,894	$733,811	$1,064,166	$1,348,145
3	REP2	$28,104	$33,301	$65,012	$73,504
4	REP3	$446,867	$682,103	$885,842	$371,260
5	REP4	$688,287	$1,099,895	$1,715,446	$2,250,357
6	REP55	$536,881	$443,793	$679,629	$838,596
7	REP66	$117,756	$681,703	$359,723	$37,412
8	REP77	$564,908	$936,422	$1,496,989	$1,945,976
9	REP88	$643,928	$925,184	$1,419,050	$1,879,872
10
11
12
13	TEAM1	TEAM2
14	REP1	REP3
15	REP2	REP4
16	REP66	REP55
17	REP88	REP77
18
19
20	TEAM1 TOTAL	$1,253,683	$2,373,999	$2,907,951	$3,338,934
21	TEAM 2 TOTAL	$2,236,944	$3,162,214	$4,777,906	$5,406,188
Sheet1

Cell Formulas
Range	Formula
B20:E20	B20	=B2+B3+B7+B9
B21:E21	B21	=B4+B5+B6+B8

 

[HighAndWilder]

Thank you again Joe, your training is very much appreciated!!!

One more question if it's ok:

I have a sheet with salereps which are assigned to two teams. How do I replace my simple formula (in rows 20 and 21) with some sort of a lookup, which basically says "lookup the rep names in a table, and total their sales as a team?" (the teams are on a different file, but I put them on the same sheet in this example for simplicity):

Book1.xlsx
	A	B	C	D	E
1	SLSREP	JAN SALES	FEB SALES	MAR SALES	APR SALES
2	REP1	$463,894	$733,811	$1,064,166	$1,348,145
3	REP2	$28,104	$33,301	$65,012	$73,504
4	REP3	$446,867	$682,103	$885,842	$371,260
5	REP4	$688,287	$1,099,895	$1,715,446	$2,250,357
6	REP55	$536,881	$443,793	$679,629	$838,596
7	REP66	$117,756	$681,703	$359,723	$37,412
8	REP77	$564,908	$936,422	$1,496,989	$1,945,976
9	REP88	$643,928	$925,184	$1,419,050	$1,879,872
10
11
12
13	TEAM1	TEAM2
14	REP1	REP3
15	REP2	REP4
16	REP66	REP55
17	REP88	REP77
18
19
20	TEAM1 TOTAL	$1,253,683	$2,373,999	$2,907,951	$3,338,934
21	TEAM 2 TOTAL	$2,236,944	$3,162,214	$4,777,906	$5,406,188
Sheet1

Cell Formulas
Range	Formula
B20:E20	B20	=B2+B3+B7+B9
B21:E21	B21	=B4+B5+B6+B8

One idea but you will need to adapt it to your two sheet scenario.

Compare two ranges in Excel to see if they match exactly.xlsm
	A	B	C	D	E	F	G
1	SLSREP	TEAM	JAN SALES	FEB SALES	MAR SALES	APR SALES
2	REP1	TEAM1	£463,894.25	£733,810.64	£1,064,166.43	£1,348,144.62
3	REP2	TEAM1	£28,104.41	£33,300.73	£65,011.75	£73,504.36
4	REP3	TEAM2	£446,866.99	£682,103.21	£885,841.76	£371,259.52
5	REP4	TEAM2	£688,287.34	£1,099,894.80	£1,715,445.96	£2,250,356.60
6	REP55	TEAM2	£536,880.95	£443,793.19	£679,628.86	£838,595.90
7	REP66	TEAM1	£117,756.32	£681,703.49	£359,722.68	£37,412.49
8	REP77	TEAM2	£564,908.25	£936,422.39	£1,496,989.27	£1,945,975.69
9	REP88	TEAM1	£643,927.55	£925,183.87	£1,419,050.09	£1,879,872.31
10
11
12	REP1	TEAM1
13	REP2	TEAM1
14	REP66	TEAM1
15	REP88	TEAM1
16	REP3	TEAM2
17	REP4	TEAM2
18	REP55	TEAM2
19	REP77	TEAM2
20
21		TEAM1	£1,253,682.53	£2,373,998.73	£2,907,950.95	£3,338,933.78
22		TEAM2	£2,236,943.53	£3,162,213.59	£4,777,905.85	£5,406,187.71
23
Sheet5

Cell Formulas
Range	Formula
B2:B9	B2	=VLOOKUP($A2,$A$12:$B$19,2,FALSE)
C21:F22	C21	=SUMIF($B$2:$B$9,$B21,C$2:C$9)