Called procedure ignoring "With Sheets" command
 

I have a workbook with two worksheets: Tally Sheet and Catalyst.
I have 2 procedures in this workbook: TallySheetRep and BanSum.
BanSum take a list of data and deletes all the duplicates.
TallySheetRep takes the same data (after BanSum runs), formats it, and
places in my Tally Sheet worksheet.
I call BanSum from TallySheetRep. Here's the problem. If I run
TallySheetRep while in the Catalyst worksheet everything runs fine. But if I
run TallySheetRep while I'm in the Tally Sheet worksheet the code starts
deleting all the duplicate lines in the Tally Sheet worksheet. Here's the
basic layout of my two procedures:

Sub TallySheetRep()
Call BanSum
With Sheets("Catalyst")
...sort, edit, etc.
copy data to sheet Tally Sheet
With Sheets("Tally Sheet")
...populate some formulas
End Sub

Sub BanSum()
With Sheets("Catalyst")
...sort, delete dups, etc.
End Sub

It's like BanSum totally disregards 'With Sheets("Catalyst")' and runs the
code right on the Tally Sheet worksheet instead. Even if I'm on the Tally
Sheet worksheet shouldn't the BanSum code 'go to' the Catalyst worksheet when
it's called? Why is this happening?

Called procedure ignoring "With Sheets" command
 

As you don't post your entire code this is only a guess.

You may miss leading dots which shall set the reference to Catalyst sheet
using the With statement.

With Sheets("Catalyst")
.Range("A1").ClearContents 'Clear A1 on sheet "Catalyst"
Range("A1").ClearContents 'Clear A1 on active sheet

Hopes this helps.

---
Per

"Bishop" skrev i meddelelsen
...
I have a workbook with two worksheets: Tally Sheet and Catalyst.
I have 2 procedures in this workbook: TallySheetRep and BanSum.
BanSum take a list of data and deletes all the duplicates.
TallySheetRep takes the same data (after BanSum runs), formats it, and
places in my Tally Sheet worksheet.
I call BanSum from TallySheetRep. Here's the problem. If I run
TallySheetRep while in the Catalyst worksheet everything runs fine. But
if I
run TallySheetRep while I'm in the Tally Sheet worksheet the code starts
deleting all the duplicate lines in the Tally Sheet worksheet. Here's the
basic layout of my two procedures:

Sub TallySheetRep()
Call BanSum
With Sheets("Catalyst")
...sort, edit, etc.
copy data to sheet Tally Sheet
With Sheets("Tally Sheet")
...populate some formulas
End Sub

Sub BanSum()
With Sheets("Catalyst")
...sort, delete dups, etc.
End Sub

It's like BanSum totally disregards 'With Sheets("Catalyst")' and runs the
code right on the Tally Sheet worksheet instead. Even if I'm on the Tally
Sheet worksheet shouldn't the BanSum code 'go to' the Catalyst worksheet
when
it's called? Why is this happening?

Called procedure ignoring "With Sheets" command
 

That was EXACTLY the problem. I had a few Cells(#,#) references in the
BanSum code and I didn't put a . in front of them. Thanks!!!

"Per Jessen" wrote:

As you don't post your entire code this is only a guess.

You may miss leading dots which shall set the reference to Catalyst sheet
using the With statement.

With Sheets("Catalyst")
.Range("A1").ClearContents 'Clear A1 on sheet "Catalyst"
Range("A1").ClearContents 'Clear A1 on active sheet

Hopes this helps.

---
Per

"Bishop" skrev i meddelelsen
...
I have a workbook with two worksheets: Tally Sheet and Catalyst.
I have 2 procedures in this workbook: TallySheetRep and BanSum.
BanSum take a list of data and deletes all the duplicates.
TallySheetRep takes the same data (after BanSum runs), formats it, and
places in my Tally Sheet worksheet.
I call BanSum from TallySheetRep. Here's the problem. If I run
TallySheetRep while in the Catalyst worksheet everything runs fine. But
if I
run TallySheetRep while I'm in the Tally Sheet worksheet the code starts
deleting all the duplicate lines in the Tally Sheet worksheet. Here's the
basic layout of my two procedures:

Sub TallySheetRep()
Call BanSum
With Sheets("Catalyst")
...sort, edit, etc.
copy data to sheet Tally Sheet
With Sheets("Tally Sheet")
...populate some formulas
End Sub

Sub BanSum()
With Sheets("Catalyst")
...sort, delete dups, etc.
End Sub

It's like BanSum totally disregards 'With Sheets("Catalyst")' and runs the
code right on the Tally Sheet worksheet instead. Even if I'm on the Tally
Sheet worksheet shouldn't the BanSum code 'go to' the Catalyst worksheet
when
it's called? Why is this happening?