skull_eagle
Board Regular
- Joined
- Mar 25, 2011
- Messages
- 89
Hi,
I have the following code which deletes a column and adds headings to all sheets.
The problem is there are 2 sheets I don't want it to do this to, I've tried to add an array and an if function to protect these sheets but I'm obviously doing something wrong.
<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> currentsheet <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ActiveWorkbook.Worksheets.Count<br><SPAN style="color:#00007F">Set</SPAN> currentsheet = ActiveWorkbook.Worksheets(i)<br>Worksheets(i).Activate<br><SPAN style="color:#007F00">'protectsh = Array("Master Box List", "Review")</SPAN><br><SPAN style="color:#007F00">'If ActiveSheet.Name <> protectsh Then</SPAN><br>****Columns("D:D").Select<br>****Selection.Delete Shift:=xlToLeft<br>****Range("A1").Select<br>****ActiveCell.FormulaR1C1 = "CLIENT NAME"<br>****Range("B1").Select<br>****ActiveCell.FormulaR1C1 = "DATE RANGE"<br>****Range("C1").Select<br>****ActiveCell.FormulaR1C1 = "PARTNER / MANAGER"<br>****Range("D1").Select<br>****ActiveCell.FormulaR1C1 = "BOX NUMBER"<br>****Range("E1").Select<br>****ActiveCell.FormulaR1C1 = "FILE NUMBER"<br>****Range("F1").Select<br>****ActiveCell.FormulaR1C1 = "REVIEW DATE"<br>****Cells.Select<br>****Cells.EntireColumn.AutoFit<br>****Range("A1").Select<br><br><SPAN style="color:#007F00">'End If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> i<br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN></FONT>
Any help would be greatly appreciated.
Thanks
I have the following code which deletes a column and adds headings to all sheets.
The problem is there are 2 sheets I don't want it to do this to, I've tried to add an array and an if function to protect these sheets but I'm obviously doing something wrong.
<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> currentsheet <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ActiveWorkbook.Worksheets.Count<br><SPAN style="color:#00007F">Set</SPAN> currentsheet = ActiveWorkbook.Worksheets(i)<br>Worksheets(i).Activate<br><SPAN style="color:#007F00">'protectsh = Array("Master Box List", "Review")</SPAN><br><SPAN style="color:#007F00">'If ActiveSheet.Name <> protectsh Then</SPAN><br>****Columns("D:D").Select<br>****Selection.Delete Shift:=xlToLeft<br>****Range("A1").Select<br>****ActiveCell.FormulaR1C1 = "CLIENT NAME"<br>****Range("B1").Select<br>****ActiveCell.FormulaR1C1 = "DATE RANGE"<br>****Range("C1").Select<br>****ActiveCell.FormulaR1C1 = "PARTNER / MANAGER"<br>****Range("D1").Select<br>****ActiveCell.FormulaR1C1 = "BOX NUMBER"<br>****Range("E1").Select<br>****ActiveCell.FormulaR1C1 = "FILE NUMBER"<br>****Range("F1").Select<br>****ActiveCell.FormulaR1C1 = "REVIEW DATE"<br>****Cells.Select<br>****Cells.EntireColumn.AutoFit<br>****Range("A1").Select<br><br><SPAN style="color:#007F00">'End If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> i<br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN></FONT>
Any help would be greatly appreciated.
Thanks