How to specify macro for current active sheet [Solved/Closed]

seo5906 - May 22, 2009 at 04:42 PM - Latest reply:  Yuune5
- May 27, 2018 at 09:34 PM
Hello,
this is a very basic question, I am sorry to ask. I have created a macro in excel that I want to run in the current worksheet that is selected. How do I do this? For example, I have a sortZip Macro that I want to run on several worksheets when I hit the RUN button but it only works on the worksheet that I originally created the macro on.

I would really appreciate your advice on this!!! - Please :D

Code is:
Sub SortZip()
'
' SortZip Macro
'

'
ActiveWorkbook.Worksheets("GilbertApril09").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GilbertApril09").Sort.SortFields.Add Key:=Range( _
"H2:H390"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("GilbertApril09").Sort.SortFields.Add Key:=Range( _
"L2:L390"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("GilbertApril09").Sort
.SetRange Range("A1:L390")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
See more 

19 replies

Best answer
95
Thank you
Very Easy!

Make a "find and replace" in your module, change every

Set shtJT = ActiveWorkbook.Sheets("xxxxx")

to the following:

Set shtJT = ActiveWorkbook.ActiveSheet

done.

Tony

Thank you, tony 95

Something to say? Add comment

CCM has helped 1364 users this month

Many thanks! I knew it would be something simple :)
Thank you! Appreciate the question and answer!
Brilliant, and, as always, once you see it there is a "how obvious" moment. This is going to make my life SO much easier. Thanks very much for this.
Even almost a decade later, the question and answer is still valid and useful!
So easy, and yet so helpful,
I was fearing I would have to rewrite the whole thing in a new workbook or create one workbook for each project but now I can add sheets along with the projects
Doc Immortal - Aug 19, 2009 at 09:58 AM
17
Thank you
One thing I noticed is you may have to have ActiveSheet proceed each method.

As an example, here's a short macro that saves the data from each cell in row H into a word document.
The macro is saved in an empty Excel file and it works for any Excel file loaded (assuming the empty Excel file with the macro is also loaded).

Without ActiveSheet preceeding things like Range("H" & i).Copy, the macro will try to do stuff with the Empty Excel file that has the macro.

Sub saveToWord

    Dim I As Integer
    Dim finalRow as Integer
    Dim appWD As Object
    Dim myString as String
    Dim numberOne as String
    Dim numberTwo as Strng
    Dim myFilename As String

    ' Create a new instance of Word & make it visible
    Set appWD = CreateObject("Word.Application")
    appWD.Visible = True

    'Find the last row with data in the database
    finalRow = ActiveSheet.Range("A9999").End(xlUp).Row
    For I = 2 To finalRow
        myString = ActiveSheet.Range("a" & i).Value

       ' these two cells are both alphanumeric cells; first two characters are always numbers
       numberOne = Left(ActiveSheet.Range("b" & i).Value, 2) 
       numberTwo = Left(ActiveSheet.Range("c" & i).Value, 2) 

       ' Build the unique filename. 
       ' In my Excel file these three elements are sufficient to make the filename unique
       myFilename = myString & " " & numberOne & " " & numberTwo

       ' We want to put the data from cell H into a word doc
       ActiveSheet.Range("H" & i).Copy
    
       ' Tell Word to create a new document
       appWD.Documents.Add
       ' Tell Word to paste the contents of the clipboard into the new document
       appWD.Selection.Paste
       ' Save the new document with the filename and close the word document
       appWD.ActiveDocument.SaveAs filename:= myFilename
       appWD.ActiveDocument.Close
       
    Next i

    ' Close the Word application
    appWD.Quit
End Sub
8
Thank you
There's no need for the .name part, just ActiveSheet works fine in Excel 2007.
aquarelle 7178 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - May 22, 2009 at 04:46 PM
7
Thank you
Hi,
Insert a module, cut and past your macro in it.
Best regards
5
Thank you
Thanks for your reply but this doesn't help me to run the macro on ANY future sheets. I don't want to copy the module each time, if that's what you are implying. The names of the worksheets will change frequently so I want to be able to click on any worksheet and run the Macro. Thanks again.
aquarelle 7178 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Jul 13, 2009 at 07:14 AM
Hi,
You have to put the macro into the personal.xls Workbook after that this wil run on any workbooks and worksheets.
Best regards
5
Thank you
Maybe ActiveSheet.Name
2
Thank you
This is an example of a macro I created using the active sheet and active cell properties. It copies the date from the previous cell.

Sub copySameDate()
Debug.Print ActiveCell.Column
Debug.Print ActiveCell.Row

Dim activeRow As Integer
Dim activeColumn As Integer
Dim previousRow As Integer
Dim nextColumn As Integer
Dim wksheet As Worksheet

Set wksheet = ActiveWorkbook.ActiveSheet

activeRow = ActiveCell.Row
activeColumn = ActiveCell.Column
previousRow = activeRow - 1
nextColumn = activeColumn + 1

wksheet.Range(ActiveCell.Address) = wksheet.Cells(previousRow, activeColumn)
wksheet.Cells(activeRow, nextColumn).Select

End Sub

This works on every sheet of my workbook. I use it for data entry.
vcoolio 1198 Posts Thursday July 24, 2014Registration dateModeratorStatus October 2, 2018 Last seen - Sep 16, 2016 at 03:05 AM
Thanks for sharing this with us Heather.
1
Thank you
Hi seo5906, did you find any solution to this? I am facing the same issue. I have to copy data from one fixed specified sheet and paste them to different current sheets every time I run the macro.

e.g.
Copy from sheet 1 (fixed sheet) and paste to sheet 2 (current sheet)
Copy from sheet 1 (fixed sheet) and paste to sheet 3 (current sheet)
Copy from sheet 1 (fixed sheet) and paste to sheet 4 (current sheet)

The issue that I am facing is - if I create the macro on sheet 2, running the macro pastes the data to sheet 2 everytime, no matter whatever be the current sheet.

Thnx