Select multiple sheets listed in a colum

[Lidget]

Hi, I'm new to vba and have mainly been learning by recording.

I currently have a book with 50 named sheets and need to delete info from about 30 of them. I currently have a macro to do this using a select array "sheet 1", "sheet 4"... Etc. I'd rather have this as a list of sheet names on a control sheet so that can be update by other people rather than them going into the code but I can't get it working.

In using:
Dim arrayworksheets As Variant

Arrayworksheets = sheets("control").range("c4:c:100")
Sheets(array(arrayworksheets)).select

...and then just range and clear contents but I get a type mismatch error

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Lidget()
   Dim Cl As Range
   Dim UsdRws As Long
   
   With Sheets("Control")
      For Each Cl In .Range("C4", .Range("C" & Rows.Count).End(xlUp))
         If Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets(Cl.Value).Range("A1:F10").ClearContents
         End If
      Next Cl
   End With
End Sub

 

[Lidget]

Hi & welcome to MrExcel.
How about

Sub Lidget()
   Dim Cl As Range
   Dim UsdRws As Long
  
   With Sheets("Control")
      For Each Cl In .Range("C4", .Range("C" & Rows.Count).End(xlUp))
         If Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets(Cl.Value).Range("A1:F10").ClearContents
         End If
      Next Cl
   End With
End Sub

Thanks for the speedy response. And I understand some of this! Unfortunately it's giving me a type mismatch error...

 

[Fluff]

Do you have any bank cells in col C?

 

[Lidget]

Thanks for the speedy response. And I understand some of this! Unfortunately it's giving me a type mismatch error...

Ignore that, I missed a bracket >_< it's giving that error when there are gaps in the list though...

 

[Lidget]

Do you have any bank cells in col C?

Yeah, I can probably get around that by filtering first though so it's not a major issue

 

[Fluff]

You can ignore blanks like

Sub Lidget()
   Dim Cl As Range
   Dim UsdRws As Long
   
   With Sheets("Control")
      For Each Cl In .Range("C4", .Range("C" & Rows.Count).End(xlUp))
         If Cl.Value <> "" Then
            If Evaluate("isref('" & Cl.Value & "'!A1)") Then
               Sheets(Cl.Value).Range("A1:F10").ClearContents
            End If
         End If
      Next Cl
   End With
End Sub