J
JOUIOUI
I have a dozen sheets in my workbook and I have one macro to format these
sheets. I've got the code I need for each sheet but when I run the macro, it
runs all the code on the first sheet in the workbook, not on the sheet
specified. For instance, in the code below, I'm wanting this to run on the
sheet titled "GESA CARD MATCHES", however it is not, it is running on the
first sheet in the workbook. Any idea what I'm doing wrong? Thanks
Sub GESACardMatches()
' Create GESA Credit Card REport with Just GESA Card items
Dim rng As Range, cell As Range
Dim i As Long, sh As Worksheet
With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1
Set sh = Worksheets("GESA CARD MATCHES")
For Each cell In rng
If UCase(Trim(cell.Value)) = "4-$" Or _
UCase(Trim(cell.Value)) = "CNO-$" Then
If UCase(Trim(cell.Offset(0, 1).Value)) = _
"GESA CC" Then
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1
End If
End If
Next
With ActiveSheet
xLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(xLastrow + 2, 5) = "Total"
.Cells(xLastrow + 2, 5).Font.Bold = True
.Cells(xLastrow + 2, 6).Formula = "=sum(F2:F" & xLastrow & ")"
.Cells(xLastrow + 2, 6).Font.Bold = True
sheets. I've got the code I need for each sheet but when I run the macro, it
runs all the code on the first sheet in the workbook, not on the sheet
specified. For instance, in the code below, I'm wanting this to run on the
sheet titled "GESA CARD MATCHES", however it is not, it is running on the
first sheet in the workbook. Any idea what I'm doing wrong? Thanks
Sub GESACardMatches()
' Create GESA Credit Card REport with Just GESA Card items
Dim rng As Range, cell As Range
Dim i As Long, sh As Worksheet
With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1
Set sh = Worksheets("GESA CARD MATCHES")
For Each cell In rng
If UCase(Trim(cell.Value)) = "4-$" Or _
UCase(Trim(cell.Value)) = "CNO-$" Then
If UCase(Trim(cell.Offset(0, 1).Value)) = _
"GESA CC" Then
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1
End If
End If
Next
With ActiveSheet
xLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(xLastrow + 2, 5) = "Total"
.Cells(xLastrow + 2, 5).Font.Bold = True
.Cells(xLastrow + 2, 6).Formula = "=sum(F2:F" & xLastrow & ")"
.Cells(xLastrow + 2, 6).Font.Bold = True