Copying the macro button to another sheet

**Heyy** · 02-17-2021, 02:10 PM

Hi,

I am trying to copy a macro button to another sheet so that the macro could be used in the "Report" sheet without a need to return back to the "Analysis" sheet. I just tried to copy the macro button to the another sheet but then the macro does not work on the "Report" sheet and it gives an error when I try to run it. Same thing happens when I created a new button and assigned the same macro to the new button in the "Report" sheet. Do you have any tips how I could copy the macro button to the "Report" sheet from the "Analysis" sheet?

**dangelor** · 02-17-2021, 02:51 PM

Assuming the two worksheets are the same (they are not in the posted workbook), possibly...

Public Sub Breakeventest()

    'Not Tested

    With ActiveSheet
        .Range("F2").Value = .Range("B2").Value
        .Range("D14").ClearContents
        .Range("H25").GoalSeek Goal:=0, ChangingCell:=.Range("B2")
        .Range("H27").Value = .Range("B2").Value
        .Range("E2").ClearContents
        .Range("B2").Value = .Range("F2").Value
        .Range("F2").ClearContents
        .Range("G2").Select
    End With
End Sub

**MarvinP** · 02-17-2021, 03:38 PM

Hey Heyy,

When you record a macro or write a normal macro it defaults to the ACTIVE worksheet. If you copy a button that works on one sheet to another, the button will call the VBA code but the sheet you press the button from is also very important!!!!

If your macro copies a cell in Range("A2") from a sheet it is from the Active sheet. If you want the value on Sheets1, you need to use something like Worksheets("Sheet1").Range("A2")

I recently learned that using the "Set" statement very useful when writing code that uses other sheets. Something like:

Dim WS1 as worksheet
Set WS1 = Worksheets("Sheet1") ' and then do you code like:
TempVar = WS1.Range("A2").Value

Hope this helps explain your concern.

**Heyy** · 02-18-2021, 06:36 AM

Hi,

Thank you for your answers. I am very beginner with macros and thus, I wasn't able to get the macro working on the second sheet despite your advices.

The case is that the analysis sheet is the main sheet and the macro is recorded there and the main button is there. But I would need to copy the same button to the report sheet so that the user can run the same macro from the report sheet without need to return to the analysis sheet. The macro's result is visible in the cell H27 and in the analysis sheet I just have done a formula =Analysis!H27 so the macro's result is copied there. Now I just would need to get the same button working in the report sheet so it would run the macro in the analysis sheet and copy the outcome to the report sheet. Any other tips? I hope my expiation wasn't too confusing.

**dangelor** · 02-18-2021, 07:46 AM

Try...

Sub Breakeventest()
    '
    ' Breakeventest Macro
    '

    '
    With Worksheets("Analysis")
        .Activate
        .Range("B2").Select
        Selection.Copy
        .Range("F2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("D14").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("D13").Select
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        .Range("H25").GoalSeek Goal:=0, ChangingCell:=.Range("B2")
        .Range("B2").Select
        Selection.Copy
        .Range("H27").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("E2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("F2").Select
        Selection.Copy
        .Range("B2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("F2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("G2").Select
    End With
End Sub

**Heyy** · 02-18-2021, 08:04 AM

Originally Posted by dangelor

Try...

Sub Breakeventest()
    '
    ' Breakeventest Macro
    '

    '
    With Worksheets("Analysis")
        .Activate
        .Range("B2").Select
        Selection.Copy
        .Range("F2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("D14").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("D13").Select
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        .Range("H25").GoalSeek Goal:=0, ChangingCell:=.Range("B2")
        .Range("B2").Select
        Selection.Copy
        .Range("H27").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("E2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("F2").Select
        Selection.Copy
        .Range("B2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("F2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("G2").Select
    End With
End Sub

Wow with this code the macro button is now working on the report sheet also! Thank you for that! But now one more issue arise, when I run the macro on the report sheet, it will automatically switch to the analysis sheet. Is there any trick to avoid excel automatically switching back to the analysis sheet when I run the macro on the report sheet?

**dangelor** · 02-18-2021, 08:09 AM

Sub Breakeventest()
    '
    ' Breakeventest Macro
    '

    '
    Dim shName As String
    shName = ActiveSheet.Name
    
    With Worksheets("Analysis")
        .Activate
        .Range("B2").Select
        Selection.Copy
        .Range("F2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("D14").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("D13").Select
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        .Range("H25").GoalSeek Goal:=0, ChangingCell:=.Range("B2")
        .Range("B2").Select
        Selection.Copy
        .Range("H27").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("E2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("F2").Select
        Selection.Copy
        .Range("B2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("F2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("G2").Select
    End With

    Worksheets(shName).Activate
End Sub

**Heyy** · 02-18-2021, 08:33 AM

Originally Posted by dangelor

Sub Breakeventest()
    '
    ' Breakeventest Macro
    '

    '
    Dim shName As String
    shName = ActiveSheet.Name
    
    With Worksheets("Analysis")
        .Activate
        .Range("B2").Select
        Selection.Copy
        .Range("F2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("D14").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("D13").Select
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        .Range("H25").GoalSeek Goal:=0, ChangingCell:=.Range("B2")
        .Range("B2").Select
        Selection.Copy
        .Range("H27").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("E2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("F2").Select
        Selection.Copy
        .Range("B2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                               :=False, Transpose:=False
        .Range("F2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        .Range("G2").Select
    End With

    Worksheets(shName).Activate
End Sub

Thank you again! One more question (sorry for asking so many questions) ; now if I press the macro button on the analysis sheet, it will jump automatically to the report sheet. Is there any way to code that if the macro button is pressed in the analysis sheet, excel will stay on that sheet and if the macro button is pressed in the report sheet, excel will stay on that sheet?

**dangelor** · 02-18-2021, 09:09 AM

??? Works for me...

**Heyy** · 02-18-2021, 09:31 AM

Originally Posted by dangelor

??? Works for me...

Ah yes, I had done a mistake. I thought that I need to replace that (shName) with the sheet's actual name so that was messing up the code. Thank you so much for your help! You're so great!

**dangelor** · 02-18-2021, 09:32 AM

Glad I could help!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Copying the macro button to another sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Re: Copying the macro button to another sheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copying ActiveX Command Button from master sheet to subsheets. command button code missing

Copying a selected row on sheet 1 to data entry sheet by pressing a button through macro

Copying a selected row on sheet 1 to data entry sheet by pressing a button through macro

Copying data as values from one Sheet to another? using a Command Button VBA

Copying a button to another sheet

How to copy sheet with a button to new file but have button point to macro in new sheet?

Code Under Button Not Recongnizing New Sheet When Copying

Bookmarks

Bookmarks

Posting Permissions