Yes, there are different kinds of macros. Some are designed to work on one sheet, you may have accidentally created one of those. Others work on the "active sheet".

The best macros can work on any sheet and dynamically figure out where they are and where the data needs to go (if to other sheets).

Some macros require you to trigger them, others will trigger themselves by some "event" that occurs.

If you post your workbook and/or your code, we can discuss the issues you're having directly.