kilbey1
09-27-2008, 11:02 AM
This macro will copy over all hot-button issues with a Y in a certain column. Here are 2 issues I am trying to resolve to polish up.
1. For some reason, even though I have told the Macro to ignore certain worksheets, it is still picking it up. Example, issue 3209 is on worksheet "New Issues" and "Release 2.0" but nowhere else. It appears I'm still grabbing from both places, which means 3209 ends up going twice on my "HOT" sheet.
2. I have a function COUNTA that will count the issues on that particular worksheet. However, after stepping into the code, I noticed that the deletion of rows changes changes the formula to begin at the last row available (in this case, A4000 to whatever). Is there some way to lock that formula, or should I consider applying the same formula within the macro and then writing it to the cell where I want the total to appear?
Sub copyHotIssues()
Dim SheetWiseMax() As Double
Dim i As Long
Dim maxRow As Integer
Dim myWorksheet As Worksheet
Set myWorksheet = Worksheets(1) 'use the name/index of your worksheet
Sheets(1).Activate
Rows.Interior.ColorIndex = xlColorIndexNone
Range("A2:M4000").Delete
Range("A2").Select
With ThisWorkbook
ReDim SheetWiseMax(1 To .Sheets.Count)
For i = 1 To .Sheets.Count
If Not Sheets(i).Name = "HOT" _
Or Not Sheets(i).Name = "New Issues" _
Or Not Sheets(i).Name = "Release 3.0" _
Or Not Sheets(i).Name = "Closed" _
Or Not Sheets(i).Name = "Deferred" Then
For Each cell In Sheets(i).Range("F:F")
If UCase(cell.Value) = "Y" Then
cell.EntireRow.Copy Sheets("HOT").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next cell
End If
Next i
End With
End Sub
1. For some reason, even though I have told the Macro to ignore certain worksheets, it is still picking it up. Example, issue 3209 is on worksheet "New Issues" and "Release 2.0" but nowhere else. It appears I'm still grabbing from both places, which means 3209 ends up going twice on my "HOT" sheet.
2. I have a function COUNTA that will count the issues on that particular worksheet. However, after stepping into the code, I noticed that the deletion of rows changes changes the formula to begin at the last row available (in this case, A4000 to whatever). Is there some way to lock that formula, or should I consider applying the same formula within the macro and then writing it to the cell where I want the total to appear?
Sub copyHotIssues()
Dim SheetWiseMax() As Double
Dim i As Long
Dim maxRow As Integer
Dim myWorksheet As Worksheet
Set myWorksheet = Worksheets(1) 'use the name/index of your worksheet
Sheets(1).Activate
Rows.Interior.ColorIndex = xlColorIndexNone
Range("A2:M4000").Delete
Range("A2").Select
With ThisWorkbook
ReDim SheetWiseMax(1 To .Sheets.Count)
For i = 1 To .Sheets.Count
If Not Sheets(i).Name = "HOT" _
Or Not Sheets(i).Name = "New Issues" _
Or Not Sheets(i).Name = "Release 3.0" _
Or Not Sheets(i).Name = "Closed" _
Or Not Sheets(i).Name = "Deferred" Then
For Each cell In Sheets(i).Range("F:F")
If UCase(cell.Value) = "Y" Then
cell.EntireRow.Copy Sheets("HOT").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next cell
End If
Next i
End With
End Sub