cell reference
 

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

cell reference
 

Hi Peter

If you want to clear the range in all sheets in the workbook, then use
Sub aaa. If you need to exclude a sheet, then look at Sub bbb, and
change 'Sheet1' to the VBA level sheet name of the sheet to exclude.
To find the VBA level sheet name, look in the project explorer window,
and use the name without paranthesis.

Sub aaa()
For Each sh In ThisWorkbook.Sheets
sh.Range("A15:D540").ClearContents
sh.Range("A1").Select
Next
End Sub

Sub bbb()
For Each sh In ThisWorkbook.Sheets
If sh.Name < Sheet1.Name Then ' Change Sheet1 as required
sh.Range("A15:D540").ClearContents
sh.Range("A1").Select
End If
Next
End Sub

Hopes this helps.
....
Per

On 8 Mar., 04:55, 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

cell reference
 

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