How do I get a macro to run on 2 pages.

[ghrek]

Hi

I was given this macro and tried to run it twice on 2 seperate commands in same macro and also tried a 2 seperate macros and it wont let me do it, Im trying to get it to run on a sheet called "WORLDLINEMACHINES" and another called "CUBIC"

Sheets("WORLDLINEMACHINES").Select
Dim c As Range, r As Range
For Each c In ActiveSheet.UsedRange
    If c.Locked = False Then
        If r Is Nothing Then
            Set r = c
        Else
            Set r = Union(r, c)
        End If
    End If
Next
If r Is Nothing Then Exit Sub
r.ClearContents
End Sub

Any Ideas?

 

[ranman256]

if you remove the sheet reference: Sheets("WORLDLINEMACHINES").Select
then it should run on whatever sheet you are on,but I would start by putting the cursor on the first cell of the range:
range("A1").select.

and what is the range for r?
i dont see it set anywhere. (if r is not nothing). You already have c range, then you copy it to r? duplicate.

 

[Logit]

This is one method :

Option Explicit

Sub RunMe()
    Sheet1.Select
    MacroIt
    Sheet2.Select
    MacroIt
End Sub

Sub MacroIt()
    With ActiveSheet
        'your macro code here
    End With
End Sub

 

[JackDanIce]

Try:

Sub ghrek()

    Dim sheet_names As String
    Dim x As Long
    Dim c As Range, r As Range
    
    sheet_names = "WORLDLINEMACHINES|CUBIC"
    
    Application.ScreenUpdating = False
    
    For x = 1 To Worksheets.Count
    
        If InStr(sheet_names, Sheets(x).Name) > 0 Then
            For Each c In Sheets(x).UsedRange
                If Not c.Locked Then
                    If r Is Nothing Then
                        Set r = c
                    Else
                        Set r = Union(r, c)
                    End If
                End If
            Next c
            
            If Not r Is Nothing Then
                r.Value = ""
                Set r = Nothing
            End If
        End If
    Next x
     
    Application.ScreenUpdating = True

End Sub