Try the below macro


Sub Macro()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Sheets("Calculations").Range("J2:J22")
If cell.Text < "" Then
Sheets(cell.Text).Range("A15:D540").ClearContents
End If
Next
Sheets(1).Activate
Application.ScreenUpdating = True
End Sub

--
Jacob


"Peter" wrote:

I have several sheets in a work book that need a range of cells deleted
before inputting more data. The range to delete is A15 to D540. Then I need
it to go to the next report and do the same, This would happen up to 20 times
as there are up to 20 reports in the workbook. Just to make this harder, The
worksheet names will be renamed as users determine different names.

I have basicly created a "Template" reporting tool that will allow different
departments to use it, simply by changing the tab names and keeping track of
the changes on the list (below)

I created a list of sheet names on a tab called "Calculations" in range J2
to J 22 and would repeat the routine up to 20 times, one for each tab
required.

If I use this routine, it works, but the reference to the sheet name would
have to be edited in the macro every time the tab is renamed.

***********


Sheets("Finance").Select
Range("A15:D540").Select
'ActiveWindow.SmallScroll Down:=-60
Selection.ClearContents
Range("A1").Select


*******************



The name Tab named Finance would have to be renamed each time the tab was
renamed.


I tried the following to reference the list , but it is not working.

****************


Sheets (Range("J2").Value).Select
Range ("A15:D540").Select
'Active Window.SmallScroll Down:=-60
Selection.ClearContents
Range("A1").Select

*****************



I wouldlike to runthe macro from one sheet and have it run through each
sheet, then retun to the starting sheet.

Do I need to reference the workbook name?

Any suggestions?

I could use some help here as I have a great tool developed and this is the
final hold up (I hope)

Peter