Calling Specific sheet in workbook with Code
 

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

Calling Specific sheet in workbook with Code
 

Is the bit that runs With Activesheet that is your problem? Should this be
With sh?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JOUIOUI" wrote in message
...
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

Calling Specific sheet in workbook with Code
 

You reference three worksheets below (GESA CARD MATCHES, All Records,
and the ActiveSheet). I ran your code and it worked. What part are
you wanting to repeat for all sheets? What part are you saying is only
working for the "first" worksheet (and by "first", do you mean the
ActiveSheet)?

Mark


JOUIOUI wrote:
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