Apply VBA Code to multiple specific sheets

[LNG2013]

I need to apply my code to 8 different sheets. Not all 8 sheets may be present. I need help with code that will look for each sheet eg. DataA, and if present will run the code on it. When the code is finished it will then check for the next sheet.

Possible Sheets:
DataA
DataB
DataC
DataD
DataE
DataF
DataG
DataH

 

[Jonmo1]

Not that I don't want to help, I certainly do.
But we also don't want to approach simply "doing it for you"

We really want to encourage you to learn.


Take some time to look at your first post (#8)
And compare it to how I changed it in post #9
Look closely at all the dots
And the With Structure(s)

See if you can't apply the same methods to the rest of the code.


Give yourself a little time and try it out.

The main parts I cleaned up was the Selecting
Selecting things seriously slows things down..

So things like

    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1:L1").Select
        With Selection
        .VerticalAlignment = xlTop
    End With

Can be canged to

Rows("1:1").Insert Shift:=xlDown
With Range("A1:L1")
    .VerticalAlignment = xlTop
End With

Feel free to post for more help with small specific parts.

 

[LNG2013]

Ok, and I totally understand about the teaching a man to fish part.
This board has been a huge resource in teaching me VBA.
Thanks and as I sift through the code I am sure I'll post smaller questions here.

Thanks again.

 

[LNG2013]

So one more small question, so I do not need to add the ws. before Range then for the remaining code?

Eg. ws.Range ?

 

[Jonmo1]

So one more small question, so I do not need to add the ws. before Range then for the remaining code?

Eg. ws.Range ?

That's right..

As long as the remaining code is within the with structure, and you use the Dot (period).

With ws
   .Range(...) '<--this line applies to the ws
End with
 
Range(...) '<--this line will not, instead it applies to whatever sheet is active

Proper indentation will significantly help you know where the With Begins and Ends.

 

[Jonmo1]

It can get tricky when you are nesting withs.. (with inside another with)

With ws
    'code here only applies to ws
    .Name = "Hello"
 
    With .Range(...)
        'code here applies to ws.Range(...)
        .Font.Bold = True
    End With
 
    'code here only applies to ws
    .Rows(1).Delete
End With

That's why indentation is so important..

 

[LNG2013]

Hey Jon!

Just wanted to say thank you again for all of the tips. I spent sometime yesterday and did the recoding. It took me a couple hours but it was a good learning experience. Thank you again!

 

[Jonmo1]

Excellent.
Glad to help, thanks for the feedback.

 

[LNG2013]

No Problem:

One small question I keep getting an error when trying to add a chart in the same code... any ideas?

"Compile error expected expression"

        With .Charts.Add
            .ChartType = xlLineMarkers
            .SetSourceData Source:=.Range("F3:F" & LastRowGraph1 & "")
            .Location Where:=xlLocationAsObject, Name:="ObjectiveA"
            .Legend.Delete
        With .PlotArea
        With .Border
                .ColorIndex = 15
                .Weight = xlThin
                .LineStyle = xlContinuous
        End With
        End With
 [COLOR=red]       With .Fill.TwoColorGradient.Style:=msoGradientVertical, Variant:=1
[/COLOR]        End With
                
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 37
        .Fill.BackColor.SchemeColor = 2
    End With

 

[Jonmo1]

Try

With .Fill
    .TwoColorGradient Style:=msoGradientVertical, Variant:=1
End With

 

[LNG2013]

Ok cool that seemed to stomp out that bug, but now I am getting an error about the chart line

Compile Error - Method or data member not found

            [COLOR=red]With .Charts.Add[/COLOR]
            .ChartType = xlLineMarkers
            .SetSourceData Source:=.Range("F3:F" & LastRowGraph1 & "")
            .Location Where:=xlLocationAsObject, Name:="ObjectiveA"
            .Legend.Delete
        With .PlotArea
        With .Border
                .ColorIndex = 15
                .Weight = xlThin
                .LineStyle = xlContinuous
        End With