Macro only runs correctly from specific sheet

[nickytraps]

Hi all,

The below is a portion of code that appears to be working (I'm sure it is far from the most efficient). The issue is that the code only runs correctly (e.g. returns the desired result) when run from a specific sheet ("Sales Calc"). Any help would be greatly appreciated. Thank you.

Please Login or Register  to view this content.

[6StringJazzer]

What module contains this code?

What does the code do incorrectly when it is not run from sheet "Sales Calc"?

You have qualified all your references except a couple, and your problem is probably in those couple. See my notes below.

Please Login or Register  to view this content.

[nickytraps]

Thank you for taking the time to look at this 6StringJazzer.

So I believe this is in a standard module and is basically filtering a column on sales calc and moving/copying certain filtered column data from that sheet to another sheet (New Inv Ids).

In short, when this code is run from the sales calc tab it correctly copies the filtered/visible cells below the header and pastes/transfers to the New Inv Id tab.
If the code is run from another sheet it grabs the correct column but from the header to the top of the sheet (not the data below).

The below might be a little cleaner but is still not working when run from another sheet.

Please Login or Register  to view this content.

[jolivanes]

This will give you the row number of the sheet that is active when you run the code.

Please Login or Register  to view this content.

This will give you the row number of the sheet as in the code line even if another sheet is the active sheet when running the code.

Please Login or Register  to view this content.

Depending on what you want to do, you might want to change to reference the right sheet.

[nickytraps]

Thank you jolivanes! I switched the last row command and it seems to be working fine.

[jolivanes]

Good to hear that you have it under control.
I made this for you so I might as well show it.

To illustrate the problem:
In an empty workbook, fill Sheet1, Column C from 1 to 25 with data.
Fill Sheet2, Column C from 1 to 20 with data.
Fill Sheet3, Column C from 1 to 15 with data.

Paste both macros into Module1.
Run this macro from any sheet and you will get the right results.

Please Login or Register  to view this content.

Now have Sheet1 as active sheet and run this macro.

Please Login or Register  to view this content.

Go to Sheet2 to see the result.

[nickytraps]

I got it. Thank you for taking the time to help both with my original question and teaching me something new. Much appreciated!