Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |