Apply macro to any sheet in the same workbook

Question

I have a bunch of sheets in the same workbook and I want to be able to click a button and run a Macro in the sheet I clicked the Macro Button.

This is one of my codes:

Sub PromoCC()  
PromoCC Macro  
    Sheets("Tipos de Proyecto").Select  
    Rows("3:69").Select  
    Range("Promoción_Nuevo_SKU_Compra_Item").Activate  
    Selection.Copy

I want to change this next line to be able to paste the copied data to the macro caller sheet

    **Sheets("General").Select**  
    Range("A4").Select  
    Selection.End(xlDown).Select  
    Selection.Insert  
    Shift:=xlDown  
    Range("C5").Select  
End Sub

I need a solution to the active sheet code, cause the "active sheet" after the data is copied is different to the macro caller sheet.

Note: [Avoid the use of Select](https://stackoverflow.com/posts/75794331/edit) in your code. Doing this should also solve your problem — cybernetic.nomad, Mar 21 '23 at 16:43

Robert Mearns · Answer 1 · 2023-04-04T08:49:58.720

In your code, when the macro is called by clicking the button, you can record what sheet you are on and store the sheet name in a variable.

It can the be used later in your code.

Create the variables

Public CurrentWorkbook As String
Public CurrentSheet As String

Store a value in the variables

CurrentWorkbook = ActiveWorkbook.Name
CurrentSheet = ActiveWorkbook.ActiveSheet.Name

Then when you want to reference the stored values, use code like this:

Windows(CurrentWorkbook).Activate
ActiveWorkbook.Sheets(CurrentSheet).Select

Putting it all together based on your code. The code can be improved but this stays close to your existing code.

Option Explicit

Public CurrentWorkbook As String
Public CurrentSheet As String
'Create the variables

Sub PromoCC()
Dim lastrow As String
    CurrentWorkbook = ActiveWorkbook.Name
    CurrentSheet = ActiveWorkbook.ActiveSheet.Name
'Store a value in the variables

    Sheets("Tipos de Proyecto").Rows("3:69").Copy
'Copy the data

    Windows(CurrentWorkbook).Activate
    ActiveWorkbook.Sheets(CurrentSheet).Select
'Reselect the stored variables
'Only needed if you have change workbook or worksheet in your code

    Sheets(CurrentSheet).Range("A" & Sheets(CurrentSheet).Rows.Count).End(xlUp).Select
'Go to the last row with data in column A in the calling worksheet
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
'Move down one row in Column A
    Selection.Insert Shift:=xlDown
'Insert the copied data

    Range("C5").Select

End Sub

Hi Robert, Si I'm trying to apply this solution but I've two errors. 1.- If I "Set" the CurrentWorkbook = ActiveWorkbook.Name" it pops up a "Compile error: Invalid outside procedure" And 2.- If I don't set it, then the same error pops up and highlight the "ActiveWorkbook" word. ¿Any ideas why? — Andrea Segorada, Apr 03 '23 at 19:04
The example code needs to be inside a procedure (Sub). See the updated answer that has a full code example based on your code. — Robert Mearns, Apr 04 '23 at 08:51

Apply macro to any sheet in the same workbook

1 Answers1