PDA

View Full Version : VBA "ignoring" request to not copy from sheet



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

rbrhodes
09-27-2008, 12:53 PM
Hi kilbey1,

Try changing the OR's to AND's:


If Not Sheets(i).Name = "HOT" _
And Not Sheets(i).Name = "New Issues" _
And Not Sheets(i).Name = "Release 3.0" _
And Not Sheets(i).Name = "Closed" _
And Not Sheets(i).Name = "Deferred" Then

Bob Phillips
09-27-2008, 12:59 PM
Sub copyHotIssues()

Dim SheetWiseMax() As Double
Dim i As Long
Dim maxRow As Integer
Dim myWorksheet As Worksheet
Dim cell As Range

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 IsError(Application.Match(Sheets(i).Name, Array("HOT", "New Issues", "Release 3.0", "Closed", "Deferred"), 0)) 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