[Solved] How to make macros code reference data in open sheet

Thanks in advance for your help.

I created a macros that makes a chart based on my data in sheet 1 of my workbook. I have 50 sheets with data in the same tempate and format, and I'd like to be able to run this macros to create a new chart on each sheet. Unfortunately when I recorded the macros (with relative referencing on), it created a code that references sheet 1 only. In this case, the sheet is called "ABRZX."

Is there a way to edit this code to reference data within an open sheet?

Thank you!

Please Login or Register  to view this content.

You could make a copy and try this, if what you mean by open sheet as meaning the sheet that is active when you run the macro.

Add this code at the top of your macros under the Sub statement.

Please Login or Register  to view this content.

Then use find and replace in the code window to search for this.
"=ABRZX
and replace it with this.
"=" & strSheetName & "

Make sure to try it on a copy.

As an example:
This: "=ABRZX!$A$3"
Should be this after find and replace: "=" & strSheetName & "!$A$3"

Originally Posted by MontrealMTL

Solved! Thank you

Thanks for marking the thread solved and for the rep. points.