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?

 

[LearnVBA83]

For the message box I put at the end of the code right before the last End Sub. Does that sound right?

MsgBox "Complete"

 

[skillilea]

There is a lot of brilliance on this board. Watching the posts and the answers is for sure a good thing. Keep asking questions for different topics and you will see all the different/creative ideas to handle situations. I just joined in Jan. Been around it for a while . Just hanging on the coat tales of greatness around here.


This is where you would check for a value (in the if statement, don't add it it's zero...Hint: check the sum column for a value > 0 you just made it work) then it doesn't get added to the final result.

For i = 1 To .UsedRange.Rows.Count
                    If InStr(1, ws.Range("C" & i).Value, "Expense", vbTextCompare) > 0 And ws.Range("B" & i).Value <> "" Then
                        Set fillRangeRow = ws.Range(ws.Cells(i, 1), ws.Cells(i, 15))
                        Set rng = mstTemp.Cells(lr, 1)
                        Set rng = rng.Resize(1, fillRangeRow.Columns.Count)
                        rng.Value = fillRangeRow.Value
    
                        lr = lr + 1
                    End If
                Next

Here is a good link on message boxes.

MessageBox


I'd also research error checking. Ie. file not found for the workbook.

Might want to clear the master sheet before you write to it.

Create a shape and assign the macro to it on your master tab.

Some ideas

 

[LearnVBA83]

Great idea! That was going to be my last question on the naming. The Data will probably have a different title each week so would you copy and paste the file name in the code each month or would you create a folder called Macro and put the master in the folder and write the code to open and execute on any other file in that folder? So then all i'd have to do is drop the file in the macro folder open the master and run right? Is that possible or would you just add the data path in the code each week.

 

[skillilea]

Nope I would open a file dialog box. Browse for whatever file you are looking for and let it run. Keep the master file in your files or desktop.

So the shape you click, opens a file dialog picker, then runs the code.

make sense?

 

[LearnVBA83]

Nope I would open a file dialog box. Browse for whatever file you are looking for and let it run. Keep the master file in your files or desktop.

So the shape you click, opens a file dialog picker, then runs the code.

make sense?

I'm picturing my master template:

Add code at the beginning to clear master template and open dialog box to look up the file and then run it. Does that sound right? I also changed your save from True to False at the bottom b/c i'd like for the original data set to go back to what it was before the macro b/c we use that spreadsheet for other stuff.

 

[LearnVBA83]

Thanks

I am on purpose writing this top down so you can learn a bit caught that in your LearnVBA83. For production I would most likely break this out into smaller subs/functions and apply a lot more error checking. Let's get it running first.

Error was Activesheet on the filter. Also, I am changing where the master sheet gets built up.

Give me a bit.

sk

So what are you suggesting here when you say break into smaller subs/functions?

 

[LearnVBA83]

What rules do you use (in your mind) to determine when to use string vs long vs integer vs double. I read where Double is for real numbers so why string or long and not double?

 

[skillilea]

All depends on your data. Double is needed for decimals. String is for text. Int and long are dependent on size of number. Variant can be anything but, don't get lazy. Use what is required. The smaller the better for performance.

Here is a reference.

Var Types

 

[skillilea]

So what are you suggesting here when you say break into smaller subs/functions?

I wrote everything in 1 sub so you could unpack it easier and digest it.

The idea "most of the time" is to use some smaller helper functions (func. returns a value, Subs execute ). VBA is pretty loose, you can do either with both but, best practice is to follow common coding standards for them.

I would break out certain sections of that code into smaller sections. Mostly for reuse and the readability.

Example instead of putting app... at the top and bottom of all the subs i would create utility subs. That way I can call 1 line of code instead of 3-5. I then can reuse that anywhere.

Application.ScreenUpdating = False
Application.EnableEvents = False

?...

Application.ScreenUpdating = True
Application.EnableEvents = true


Public Sub ApplicationLock()
' locks the application for faster processing

    If applicationIsLocked Then Exit Function
    
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .StatusBar = False
        .EnableEvents = False
        iWindowState = .WindowState
    End With
    applicationIsLocked = True
End Function

Public Sub ApplicationUnLock()
' unlocks application for normal activity

    If applicationIsLocked Then
        With Application
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
            .StatusBar = True
            .EnableEvents = True
            .WindowState = iWindowState
        End With
    
        applicationIsLocked = False
    End If
End Function

 

[skillilea]

Hey, to keep this thread clean. It's probably better if you have other questions to start a personal conversation. Click under my name for that.