How To make any open sheet in WorkBK run Macro.

shilabrow · #1 06-22-2014, 09:13 PM

Thanks Whatsup. I am a bit confused with the entering a formula in sheet 2 and putting the code in. Like I said If sheet 1 is not open where the saved macro is (which I recorded), I get error - I am looking for a way to make the macro run regardless of which sheet is open. Your assistance in getting this right will be appreciated. Below is my sample code, kindly modify to make it clearer. Thanks

Sub Trials()

'
' Trials Macro
'

'
Selection.AutoFilter
ActiveSheet.ListObjects("Table_Query_from_MS_Acces s_Database").Range. _
AutoFilter Field:=6, Criteria1:="Owner"
ActiveSheet.ListObjects("Table_Query_from_MS_Acces s_Database").Range. _
AutoFilter Field:=13, Criteria1:="WA"
Cells.Select
Range("Table_Query_from_MS_Access_Database[[#Headers],[First Name]]").Activate
Selection.Copy
Sheets("Result").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
End Sub

whatsup · #2 06-22-2014, 10:41 PM

QueryTables - hm

I saw your other thread afterwards...

Anyway, try if this works:

Code:

Sub Trials()
'
' Trials Macro
'
 '
With Sheets(1)
    .Range("A3").AutoFilter 'replace Range A3 by the first cell you're autofilter starts
    .ListObjects("Table_Query_from_MS_Acces s_Database").Range. _
        AutoFilter Field:=6, Criteria1:="Owner"
    .ListObjects("Table_Query_from_MS_Acces s_Database").Range. _
        AutoFilter Field:=13, Criteria1:="WA"
    .Range("Table_Query_from_MS_Access_Database[[#Headers],[First Name]]").Copy _
        Destination:=Sheets("Result").Range("A1")
End With
End Sub

Basically it's important that you can't use ActiveSheet in a macro if you start the macro from another sheet - obvious isn't it? Therefore you have to refer explicitly to the sheets name, or as above the sheets position in your workbook.

macropod · #3 06-22-2014, 11:44 PM

Quote:

Originally Posted by whatsup

Basically it's important that you can't use ActiveSheet in a macro if you start the macro from another sheet - obvious isn't it?

Unless, of course, your code activates the sheet concerned (e.g. ThisWorkBook.Sheets("MySheet").Activate). But even that requires the sheet to be named for activation. Not a recommended way to program - it's both inefficient and error-prone.

shilabrow · #4 06-23-2014, 07:14 PM

Thanks Whatsup - your bottom explanation spoke volume. I am just picking up with Macro and programming and thanks for Helping! I just named the sheet and referenced that, which helped.

Macropod - I appreciate your input.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to make an add-in/global macro	hanvyj	Excel Programming	4	12-14-2012 03:23 AM
Make row in one sheet equal column of another	hypertyper	Excel	6	01-31-2012 03:48 PM
confirmation popup on excel sheet macro	mark-gabb	Excel Programming	3	12-22-2011 01:16 AM
Debug for macro run through button only when sheet protected	leahca	Excel Programming	0	11-24-2011 04:47 AM
copy cell from sheet 2 to sheet 3 macro	slipperyjim	Excel Programming	1	02-18-2010 01:31 AM