How to specify macro for current active sheet [Solved/Closed]
seo5906 - - Latest reply: Yuune5 - May 27, 2018 at 09:34 PM
Related:
- Active worksheet vba
- Vba active worksheet - Best answers
- Active sheet vba - Best answers
- Vba active worksheet ✓ - Forum - Programming
- Last worksheet vba - How-To - Excel
- Create worksheet vba - How-To - Excel
- Add worksheet vba - How-To - Excel
- Excel vba copy rows to another worksheet based on criteria ✓ - Forum - Excel
6 replies
Best answer
approved by Jean-François Pillou
on Nov 29, 2018
Very Easy!
Make a "find and replace" in your module, change every
to the following:
done.
Tony
Make a "find and replace" in your module, change every
Set shtJT = ActiveWorkbook.Sheets("xxxxx")
to the following:
Set shtJT = ActiveWorkbook.ActiveSheet
done.
Tony
Glad we were able to help! Love us? Write us a review! Rate CCM
3053 users have said thank you to us this month
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.
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
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.
- Posts
- 7170
- Registration date
- Saturday April 7, 2007
- Status
Hi,
You have to put the macro into the personal.xls Workbook after that this wil run on any workbooks and worksheets.
Best regards
You have to put the macro into the personal.xls Workbook after that this wil run on any workbooks and worksheets.
Best regards
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.
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.
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