How To make any open sheet in WorkBK run Macro.

whatsup · #1 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 · #2 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 · #3 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