Macro to delete data from several sheets

[diygail123]

Hi

I have the below code which I was hoping would scroll through some worksheets in a workbook, and delete certain ranges of data. However, it is bugging at "Sheets(sName).Sheet. I am using the list of vendor codes on a separate worksheet as a collection. Can any of you guys see what I have wrong?



'DELETE OLD DATA FROM VENDOR TABS


Sub Delete()
'DECLARE VARIABLES
Dim colShts As New Collection
Dim sName As String
Dim ListOfVendorCodes As Range, c As Range
Dim i As Integer




Set ListOfVendorCodes = Worksheets("List of Vendor Codes").Range("b2:b57")


'SCROLL THROUGH VENDOR CODES
Sheets("List of Vendor Codes").Activate
Range("b2").Select
While ActiveCell.Value <> ""
colShts.Add ActiveCell.Value
ActiveCell.Offset(1, 0).Select 'next row
Wend




For i = 1 To colShts.Count
sName = colShts(i)

'DELETE THE OLD DATA
Sheets(sName).Select 'bugging here



Range("A7:k860").ClearContents
Range("L864:M864").ClearContents
Range("h868:I868").ClearContents

Next
Set colShts = Nothing
End Sub

 

[Fluff]

Try

Sub Delete()
   'DECLARE VARIABLES
   Dim colShts As New Collection
   Dim sName As String
   Dim ListOfVendorCodes As Range, Cl As Range
   Dim i As Long
   
   Set ListOfVendorCodes = Worksheets("List of Vendor Codes").Range("b2:b57")
   
   'SCROLL THROUGH VENDOR CODES
   For Each Cl In ListOfVendorCodes
      If Cl.Value <> "" Then colShts.Add Cl.Value
   Next Cl
   
   For i = 1 To colShts.Count
      sName = colShts(i)
      If Evaluate("isref('" & sName & "'!A1)") Then
      'DELETE THE OLD DATA
         With Sheets(sName)
            .Range("A7:k860").ClearContents
            .Range("L864:M864").ClearContents
            .Range("h868:I868").ClearContents
         End With
      End If
   Next i
   Set colShts = Nothing
End Sub

 

[diygail123]

Thanks again, thats working fine, can I be so bold as to ask you to take a look at this one, it is copying a filtered range on one tab, and then trying to paste it to another tab, the name of which is the number filtered on:



Sub CopyFltr()


'DECLARE VARIABLES
Dim Ws As Worksheet
Dim Cl As Range

Application.ScreenUpdating = False
Set Ws = Sheets("Import")

'FILTER ON EACH VENDOR ON IMPORT TAB
If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
With CreateObject("Scripting.dictionary")
For Each Cl In Ws.Range("B5", Ws.Range("B" & Rows.Count).End(xlUp))
If Not .Exists(Cl.Value) Then
.Add Cl.Value, Nothing
Ws.Range("A44").AutoFilter 2, Cl.Value
Ws.AutoFilter.Range.Copy


Sheets(Cl.Value).Activate 'BUGGING HERE
Range("A6").Paste
'Ws.AutoFilter.Range.Copy Sheets(Cl.Value).Range("A4")
End If
Next Cl
End With
Ws.AutoFilterMode = False
ActiveWindow.View = xlNormalView


MsgBox ("Vendor tabs have now been updated")
Sheets("Macros").Activate
End Sub

 

[Fluff]

As this is a different question please start a new thread.

Also when posting code please use code tags, the # icon in the reply window.

 

[diygail123]

Hi, I have posted as a separate post, but not sure what you mean by code tags, I have put # at the beginning of my text, tried to put it in a box but it said it had to be three characters?