VBA Macro Loop Through worksheet

LearnVBA83 · Jan 31, 2024

Is there a way to write a macro that will loop through each worksheet in workbook and copy the name of the worksheet into that worksheet in cell A1 to A200?

LearnVBA83 · Feb 1, 2024

2024 - Budget - Finance Results.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Done

2024

2024 1RF

2024 2RF

2024 3RF

2

CSO - Commercial PA Prod

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

3

Operating Days

23

21

22

23

20

23

22

21

23

21

22

4

5

CSO - Commercial PA Prod

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Full Year

6

Coordinator

15.0

7

Manager

5.0

8

RPH

174.3

180.9

203.3

172.2

167.9

188.1

135.7

169.0

156.6

152.2

167.8

160.0

169.0

9

RPH - Contingent

70.2

75.9

52.7

59.0

90.8

83.7

91.1

71.5

71.8

81.9

95.8

101.9

78.9

10

Sr Manager

1.0

11

Supervisor

44.8

43.1

44.3

38.0

41.2

44.7

38.0

40.5

38.4

40.5

47.6

47.8

42.4

12

Tech

428.9

425.3

431.9

351.6

372.1

401.0

328.7

343.8

322.9

362.3

468.6

496.1

394.4

13

Tech - Contingent

107.1

68.6

83.1

75.4

84.8

105.4

103.1

118.7

114.5

106.2

99.0

76.3

95.2

14

Sr Analyst

-

15

Consultant

-

16

Nurse Associate

10.0

17

0

-

18

0

-

19

0

-

20

0

-

21

0

-

22

Total FTEs

856.2

824.8

846.2

727.2

787.7

853.9

727.6

774.5

735.2

774.1

909.7

913.0

810.8

23

QC

-

24

25

CSO - Commercial PA Prod - Total Cost

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Full Year

26

Coordinator

$ 64,923

$ 59,277

$ 63,583

$ 66,473

$ 57,803

$ 66,473

$ 63,583

$ 60,693

$ 66,473

$ 60,693

$ 63,583

$ 752,833

27

Manager

$ 62,739

$ 64,334

$ 767,224

28

RPH

$ 2,146,169

$ 2,227,050

$ 2,502,611

$ 2,174,530

$ 2,119,411

$ 2,374,398

$ 1,712,733

$ 2,133,894

$ 1,977,175

$ 1,921,766

$ 2,118,222

$ 2,019,767

$ 25,427,726

29

RPH - Contingent

$ 1,031,463

$ 1,018,044

$ 707,026

$ 829,196

$ 1,334,297

$ 1,070,120

$ 1,338,256

$ 1,004,925

$ 963,616

$ 1,203,879

$ 1,285,267

$ 1,432,185

$ 13,218,273

30

Sr Manager

$ 10,409

$ 10,674

$ 127,294

31

Supervisor

$ 324,394

$ 312,415

$ 320,570

$ 282,184

$ 305,847

$ 331,727

$ 282,318

$ 300,671

$ 285,291

$ 300,516

$ 353,584

$ 354,848

$ 3,754,367

32

Tech

$ 1,974,704

$ 1,788,143

$ 1,815,684

$ 1,585,551

$ 1,754,189

$ 1,644,085

$ 1,549,801

$ 1,550,469

$ 1,389,852

$ 1,707,948

$ 2,016,974

$ 2,237,054

$ 21,014,454

33

Tech - Contingent

$ 489,534

$ 286,394

$ 346,902

$ 329,883

$ 387,732

$ 418,926

$ 471,517

$ 519,079

$ 477,907

$ 485,722

$ 413,316

$ 333,528

$ 4,960,439

34

Sr Analyst

$ -

35

Consultant

$ -

36

Nurse Associate

$ 66,638

$ 60,844

$ 65,263

$ 68,229

$ 59,330

$ 68,229

$ 65,263

$ 62,296

$ 68,229

$ 62,296

$ 65,263

$ 772,724

37

0

$ -

38

0

$ -

39

0

$ -

40

0

$ -

41

0

$ -

42

Expense

6,170,975

5,825,316

5,886,064

5,405,197

6,111,185

6,031,396

5,564,334

5,712,890

5,291,839

5,829,540

6,385,360

6,581,236

$ 70,795,334

001128

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C23:O23	Cell Value	<>0	text	NO

LearnVBA83 · Feb 1, 2024

2024 - Budget - Finance Results.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

43

44

CSO - Commercial PA Prod - Wages

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Full Year

45

Coordinator

51,666

47,173

49,419

51,666

44,927

51,666

49,419

47,173

51,666

47,173

49,419

$ 588,538

46

Manager

53,155

$ 637,865

47

RPH

1,818,325

1,886,850

2,120,318

1,796,687

1,751,145

1,961,826

1,415,131

1,763,112

1,633,624

1,587,843

1,750,163

1,668,815

$ 21,153,839

48

RPH - Contingent

1,031,463

1,018,044

707,026

829,196

1,334,297

1,070,120

1,338,256

1,004,925

963,616

1,203,879

1,285,267

1,432,185

$ 13,218,273

49

Sr Manager

8,819

$ 105,831

50

Supervisor

274,840

264,692

271,601

233,152

252,703

274,087

233,262

248,427

235,719

248,299

292,146

293,190

$ 3,122,119

51

Tech

1,571,466

1,423,001

1,444,918

1,232,357

1,363,430

1,277,852

1,204,571

1,205,090

1,080,252

1,327,489

1,567,678

1,738,733

$ 16,436,838

52

Tech - Contingent

489,534

286,394

346,902

329,883

387,732

418,926

471,517

519,079

477,907

485,722

413,316

333,528

$ 4,960,439

53

Sr Analyst

-

$ -

54

Consultant

-

$ -

55

Nurse Associate

53,031

48,419

50,725

53,031

46,114

53,031

50,725

48,419

53,031

48,419

50,725

$ 604,088

56

0

-

$ -

57

0

-

$ -

58

0

-

$ -

59

0

-

$ -

60

0

-

$ -

61

Expense

5,352,300

5,036,548

5,048,332

4,583,394

5,255,978

5,155,826

4,829,408

4,902,751

4,548,686

5,019,902

5,466,136

5,628,571

60,827,831

62

63

CSO - Commercial PA Prod - Merit

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Full Year

64

Coordinator

1,483

1,550

1,348

1,550

1,483

1,415

1,550

1,415

1,483

$ 13,276

65

Manager

1,595

$ 14,352

66

RPH

53,901

52,534

58,855

42,454

52,893

49,009

47,635

52,505

50,064

$ 459,850

67

RPH - Contingent

-

$ -

68

Sr Manager

265

$ 2,381

69

Supervisor

6,995

7,581

8,223

6,998

7,453

7,072

7,449

8,764

8,796

$ 69,330

70

Tech

36,971

40,903

38,336

36,137

36,153

32,408

39,825

47,030

52,162

$ 359,924

71

Tech - Contingent

-

$ -

72

Sr Analyst

-

$ -

73

Consultant

-

$ -

74

Nurse Associate

1,522

1,591

1,383

1,591

1,522

1,453

1,591

1,453

1,522

$ 13,627

75

0

-

$ -

76

0

-

$ -

77

0

-

$ -

78

0

-

$ -

79

0

-

$ -

80

Expense

-

102,729

106,018

110,003

90,589

101,362

93,215

99,909

113,027

115,886

932,739

001128

LearnVBA83 · Feb 1, 2024

2024 - Budget - Finance Results.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

81

82

CSO - Commercial PA Prod - FICA

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Full Year

83

Coordinator

4,293.4

3,920.1

4,106.7

4,293.4

3,733.4

4,293.4

4,106.7

3,920.1

4,293.4

3,920.1

4,106.7

$ 48,908

84

Manager

4,417.2

$ 53,007

85

RPH

151,102.8

156,797.3

176,198.4

149,304.7

145,520.2

163,027.7

117,597.4

146,514.6

135,754.2

131,949.7

145,438.5

138,678.5

$ 1,757,884

86

RPH - Contingent

87

Sr Manager

732.9

$ 8,795

88

Supervisor

22,839.2

21,995.9

22,570.0

19,375.0

20,999.7

22,776.6

19,384.1

20,644.3

19,588.3

20,633.7

24,277.3

24,364.1

$ 259,448

89

Tech

130,588.8

118,251.4

120,072.7

102,408.9

113,301.0

106,189.5

100,099.8

100,143.0

89,768.9

110,314.4

130,274.0

144,488.7

$ 1,365,901

90

Tech - Contingent

91

Sr Analyst

-

$ -

92

Consultant

-

$ -

93

Nurse Associate

4,406.8

4,023.6

4,215.2

4,406.8

3,832.0

4,406.8

4,215.2

4,023.6

4,406.8

4,023.6

4,215.2

$ 50,200

94

0

-

$ -

95

0

-

$ -

96

0

-

$ -

97

0

-

$ -

98

0

-

$ -

99

Expense

318,381

310,138

331,935

284,561

293,671

304,709

250,932

280,774

258,205

276,748

313,084

321,003

3,544,142

100

101

CSO - Commercial PA Prod - Benefits

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Full Year

102

Coordinator

$ 8,964

$ 8,185

$ 8,574

$ 8,964

$ 7,795

$ 8,964

$ 8,574

$ 8,185

$ 8,964

$ 8,185

$ 8,574

$ 102,111

103

Manager

$ 5,167

$ 62,000

104

RPH

$ 176,741

$ 183,402

$ 206,095

$ 174,638

$ 170,211

$ 190,689

$ 137,551

$ 171,374

$ 158,788

$ 154,338

$ 170,116

$ 162,209

$ 2,056,153

105

RPH - Contingent

106

Sr Manager

$ 857

$ 10,287

107

Supervisor

$ 26,714

$ 25,728

$ 26,400

$ 22,662

$ 24,563

$ 26,641

$ 22,673

$ 24,147

$ 22,912

$ 24,135

$ 28,397

$ 28,498

$ 303,470

108

Tech

$ 272,649

$ 246,891

$ 250,693

$ 213,814

$ 236,555

$ 221,707

$ 208,993

$ 209,083

$ 187,424

$ 230,319

$ 271,992

$ 301,670

$ 2,851,791

109

Tech - Contingent

110

Sr Analyst

$ -

111

Consultant

$ -

112

Nurse Associate

$ 9,201

$ 8,401

$ 8,801

$ 9,201

$ 8,001

$ 9,201

$ 8,801

$ 8,401

$ 9,201

$ 8,401

$ 8,801

$ 104,809

113

0

$ -

114

0

$ -

115

0

$ -

116

0

$ -

117

0

$ -

118

Expense

500,294

478,630

505,797

434,513

455,518

460,858

393,406

428,004

391,733

432,981

493,114

515,776

5,490,622

119

120

001128

LearnVBA83 · Feb 1, 2024

I put the worksheet in... in 3 sections. The tab name is 001128

Please let me know if that works! Thanks again! I'm learning a lot looking through your code. It seemed like when i was walking through the code F8 that it was deleting all the lines from this worksheet... so it may be trying to delete everything that's not contingent instead of deleting the lines that are contingent. Also, most of these worksheets have active filters so you may want the code to remove filter at the beginning.

skillilea · Feb 1, 2024

hmmm

It worked for me

The filters are being turned off prior any modifications already.

Change this part of the sub

VBA Code:

                ' delete contingent
                For i = ws.UsedRange.Rows.Count To 1 Step -1
                    If InStr(1, ws.Range("C" & i).Value, "Contingent", vbTextCompare) > 0 Then
                       ws.Cells(i, 1).EntireRow.Delete  ' more descriptive
                    End If
                Next

here are the results

Master.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

2

001128

6170975

5825316

5886064

5405197

6111185

6031396

5564334

5712890

5291839

5829540

6385360

6581236

3

001128

6120110

5352300

5036548

5048332

4583394

5255978

5155826

4829408

4902751

4548686

5019902

5466136

5628571

4

001128

6120807

0

102729.5

106018.5

110003.4

90589.05

101362.4

93214.88

99909.06

113026.6

115885.7

5

001128

6120605

318381.2

310138.4

331935

284560.6

293671.2

304709.4

250931.7

280773.9

258205.2

276748.1

313083.7

321003.4

6

001128

6030610

500293.8

478629.8

505797

434513.3

455517.9

460857.5

393405.6

428003.6

391733.1

432981.1

493113.7

515776

Master

LearnVBA83 · Feb 1, 2024

That's crazy it works for you and not me. I put in the new code and still not working... could it be the version? I have Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20796) 32-bit I wonder if the code is trying to run too fast.

I made a 1 min snagit. Let me know if you need me to redo. The Audio isn't the best.

skillilea · Feb 1, 2024

I doubt it's the version. Nothing crazy in the code.

I didn't see any reason from the video that it shouldn't have worked. I just ran it again and it worked.

See if you can get that file put up on dropbox and I'll look at it.

Login - Dropbox

Login to Dropbox. Bring your photos, docs, and videos anywhere and keep your files safe.

www.dropbox.com

LearnVBA83 · Feb 1, 2024

Does this need to be reversed? If you look at your totals you posted above they don't tie to what the totals should be without the contingent. I think it's b/c you do the paste value before deleting the contingent. I was able to get it to work once by holding down F8 for a long time!

VBA Code:

 ' overwrite with just values
                With .UsedRange
                    .Value = .Value
                End With
            
                ' delete contingent
                For i = ws.UsedRange.Rows.Count To 1 Step -1
                    If InStr(1, ws.Range("C" & i).Value, "Contingent", vbTextCompare) > 0 Then
                       ws.Cells(i, 1).EntireRow.Delete  ' more descriptive
                    End If
                Next

LearnVBA83 · Feb 1, 2024

I logged into dropbox and uploaded the file and the master template. Now how to I get it to you?

skillilea · Feb 1, 2024

Just make it public and share the link

VBA Macro Loop Through worksheet

LearnVBA83

Board Regular

LearnVBA83

Board Regular

Excel Facts

LearnVBA83

Board Regular

LearnVBA83

Board Regular

LearnVBA83

Board Regular

skillilea

Active Member

LearnVBA83

Board Regular

skillilea

Active Member

Login - Dropbox

LearnVBA83

Board Regular

LearnVBA83

Board Regular

skillilea

Active Member

Similar threads

Forum statistics

Share this page

VBA Macro Loop Through worksheet

Board Regular

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Active Member

Board Regular

Active Member

Board Regular

Board Regular

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock