VBA Macro Loop Through worksheet

[LearnVBA83]

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?

 

[skillilea]

Here is an article on leading zeros in excel.

Leading Zeros

 

[skillilea]

This is probably cleaner if you don't know the size of the number, try this

Sub CopySheetNamesWith6DigitNumbersV2()
    Dim ws As Worksheet
    Dim numericPart As String
    Dim i As Integer
    Dim char As String
    
    For Each ws In ThisWorkbook.Worksheets
        numericPart = ""
        
        For i = 1 To Len(ws.Name)
            char = Mid(ws.Name, i, 1)
            If IsNumeric(char) Then
                numericPart = numericPart & char
            End If
        Next
        ws.Range("A1:A200").Value = numericPart
    Next
End Sub

 

[LearnVBA83]

Wow this worked! Is there a way to make the macro format the column as text so that i can get all 6 digits? The reason i'm asking is this is Step 1 to a looping macro i'm trying to build. I also need the macro to add some formulas, do some editing, filtering, deleting, and then to copy and paste that total into a master spreadsheet before it loops to the next worksheet. I assume to do that i'd need to insert the other code that i want to do before the next button. If you're interested in seeing what the total objective i'm trying to accomplish, i created a snagit video below. I understand if you're not interested in looking at it. In that case i will probably post my objectives in separate threads and try to piece it together.

 

[skillilea]

Interesting.

When you are summing expenses, Why are you summing up if the formula is there? You just need the values correct? Do you trust the formulas?

Can you post those two files in a public dropbox or other online file repo?

 

[LearnVBA83]

Interesting.

When you are summing expenses, Why are you summing up if the formula is there? You just need the values correct? Do you trust the formulas?

Can you post those two files in a public dropbox or other online file repo?

Everything in those tabs is hard coded so I need to delete the contingent lines b/c I'm ONLY worried about full time employee wages, benefits, FICA, etc. So I'm removing contingent employee lines and then summing those hard coded numbers so the numbers change to the correct totals. Then I'm copy and pasting values of the whole sheet so that it locks those expense totals. Then filtering by expense and copying those 4 lines to a master template and then loop to the next tab that has a 6 digit name. I had some help and started piecing together some of the sum formula but it still has a glitch or two. What's this drobox? Does it allow me to send you a file?

Sub test()
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastRow, 15)) ' load all the dworksheet into memory
startrow = 2
For i = 2 To lastRow
 If inarr(i, 2) = "Expense" Then
  For j = 3 To 15
    tt = Chr(64 + j)
   Range(Cells(i, j), Cells(i, j)).Formula = "=Sum(" & tt & startrow & ":" & tt & i - 1 & ")"
    Next j
    startrow = i + 2
  End If
Next i
End Sub

 

[skillilea]

Don't worry about the code right now. Can you install XL2BB? Ideally there is a sample sheet we can work from. The plugin will allow you to copy one of your "555353" sheets so we can pull it into excel.

Dropbox is a way to share files online and give public access to the forum members. Login - Dropbox

 

[LearnVBA83]

Let me give it a try...

 

[LearnVBA83]

2024 - Budget - Finance Results.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Done		2023		2023 1RF			2023 2RF				2023 3RF
2		GSO - IRE	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3		Operating Days	23	21	21	22	23	20	23	22	21	23	21	22
4
5		GSO - IRE	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Full Year
6		RPH	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0
7		Supervisor	2.0	2.0	2.0	2.0	2.0	2.0	2.0	2.0	2.0	2.0	2.0	2.0	2.0
8		Tech	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0	15.0
22		Total FTEs	32.0	32.0	32.0	32.0	32.0	32.0	32.0	32.0	32.0	32.0	32.0	32.0	32.0
23		QC	-	-	-	-	-	-	-	-	-	-	-	-
24
25		GSO - IRE - Total Cost	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Full Year
26		RPH	$ 185,606	$ 185,606	$ 185,606	$ 190,324	$ 190,324	$ 190,324	$ 190,324	$ 190,324	$ 190,324	$ 190,324	$ 190,324	$ 190,324	$ 2,269,730
27		Supervisor	$ 21,356	$ 21,356	$ 21,356	$ 21,899	$ 21,899	$ 21,899	$ 21,899	$ 21,899	$ 21,899	$ 21,899	$ 21,899	$ 21,899	$ 261,162
28		Tech	$ 70,905	$ 64,740	$ 64,740	$ 69,442	$ 72,598	$ 63,129	$ 72,598	$ 69,442	$ 66,285	$ 72,598	$ 66,285	$ 69,442	$ 822,202
42		Expense	277,868	271,702	271,702	281,664	284,821	275,351	284,821	281,664	278,508	284,821	278,508	281,664	$ 3,353,094
43
44		GSO - IRE - Wages	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Full Year
45		RPH	157,253	157,253	157,253	157,253	157,253	157,253	157,253	157,253	157,253	157,253	157,253	157,253	$ 1,887,039
46		Supervisor	18,094	18,094	18,094	18,094	18,094	18,094	18,094	18,094	18,094	18,094	18,094	18,094	$ 217,128
47		Tech	56,426	51,520	51,520	53,973	56,426	49,066	56,426	53,973	51,520	56,426	51,520	53,973	$ 642,768
61		Expense	231,773	226,867	226,867	229,320	231,773	224,414	231,773	229,320	226,867	231,773	226,867	229,320	2,746,935
62
63		GSO - IRE - Merit	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Full Year
64		RPH				4,718	4,718	4,718	4,718	4,718	4,718	4,718	4,718	4,718	$ 42,458
65		Supervisor				543	543	543	543	543	543	543	543	543	$ 4,885
66		Tech				1,619	1,693	1,472	1,693	1,619	1,546	1,693	1,546	1,619	$ 14,499
80		Expense	-	-	-	6,880	6,953	6,732	6,953	6,880	6,806	6,953	6,806	6,880	61,843
81
82		GSO - IRE - FICA	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Full Year
83		RPH	13,067.7	13,067.7	13,067.7	13,067.7	13,067.7	13,067.7	13,067.7	13,067.7	13,067.7	13,067.7	13,067.7	13,067.7	$ 156,813
84		Supervisor	1,503.6	1,503.6	1,503.6	1,503.6	1,503.6	1,503.6	1,503.6	1,503.6	1,503.6	1,503.6	1,503.6	1,503.6	$ 18,043
85		Tech	4,689.0	4,281.3	4,281.3	4,485.1	4,689.0	4,077.4	4,689.0	4,485.1	4,281.3	4,689.0	4,281.3	4,485.1	$ 53,414
99		Expense	19,260	18,853	18,853	19,057	19,260	18,649	19,260	19,057	18,853	19,260	18,853	19,057	228,270
100
101		GSO - IRE - Benefits	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Full Year
102		RPH	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 15,285	$ 183,420
103		Supervisor	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 1,759	$ 21,105
104		Tech	$ 9,790	$ 8,939	$ 8,939	$ 9,364	$ 9,790	$ 8,513	$ 9,790	$ 9,364	$ 8,939	$ 9,790	$ 8,939	$ 9,364	$ 111,520
118		Expense	26,834	25,982	25,982	26,408	26,834	25,557	26,834	26,408	25,982	26,834	25,982	26,408	316,045
008179

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

 

[LearnVBA83]

I hope i did it right

 

[skillilea]

Well done! Give me a bit