Make macro on copied sheet refer to the new sheet?

**ADJetz** · 11-27-2014, 07:28 AM

Hi All, I have a workbook with a sheet, let's call it "Sheet A", with a macro assigned to a button. This macro adds selected cell contents from "Sheet A" to a separate "Overview" sheet. The macro works fine but if I copy "Sheet A" with the macro assigned button to make a new sheet, "Sheet B", in which to enter different information, the macro in the new sheet, "Sheet B", refers to the information in "Sheet A", and enters the "Sheet A" information into the "Overview" sheet when clicked.

Is there a simple way, apart from creating new macros each time or copying the macro and editing the code (which would defeat the purpose of the time saving macro), to make the copied macro refer to the information in "Sheet B"?

Thanks in advance!

**6StringJazzer** · 11-27-2014, 10:09 AM

It is certainly possible but there is more than one way to do this depending on how you have structured your existing code. I can't tell you what to change before seeing what you have. Can you attach your file? If it has sensitive data just delete all of the data in all the sheets. I just need to see all the code and where it's located.

**ADJetz** · 11-27-2014, 10:57 AM

No sensitive info, it's a project for college so it has fictional info in it. But i would like to get it working as I could use it in my personal life myself.

Ill attatch the file now

I found this on another forum...

First, declare your Public Variable:
Code:

Public MyPrevSheet As String
Now, in the ThisWorkbook VBA module, create a Workbook_SheetDeactivate to capture your sheet name as you move off it like this:
Code:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MyPrevSheet = Sh.Name
End Sub
Now, you can create a macro (in a Standard Module) that whenever run will go back to the previous sheet, like this:
Code:

Sub GoToPreviousSheet()

If Len(MyPrevSheet) > 0 Then
Sheets(MyPrevSheet).Activate
Else
MsgBox "You have not switched sheets yet since opening the file!"
End If

End Sub
Just assign that code to a button, and that should do it!

and this will define the name of the most recently deactivated sheet (the one im trying to take info from to put into the "Overview" sheet which gets activated I start the process of linking the data) as a String called "MyPrevSheet".

There is a similar code I saw which will print the name of the previously deactivated sheet in a message box when you change sheets.

So if there was some way to get the name of the previously deactivated sheet to be printed into the formula like "='Sheet1'B4", but instead of the "'Sheet1'", have some code to enter the name of the previously deactivated sheet or "MyPrevSheet" in it's place...

Am I making sense? Ill post the file in a little while, just leaving college now. It's be nice if there was just a button for "Update Macro To Relate To This Sheet"