How can I use a Range Variable in the Advanced Filter VBA Code

[MikeRetyred]

Hi,
My project is to create an Excel Personal Accounts WorkBook to replace MS Money.
I'm using a Apple Mac Mini with Office 365 - OK with Excel, very new to VBA, so I'm mostly adapting recorded macros. in my 80th year this is challenging - simplicity is key
Project Summary:
Sheet 1: "TransTable" - a 'flat' table containing all transactions
Sheet 2: "Reconcile" - containing transactions selected from Sheet 1 using the Advance Filter to be analysed/reconciled.
A Count value in Sheet 1 "A1" holds the number of rows / transactions.
Question:
How can I create a Variable to use in the Advanced Filter Range.
My Code so far:
Sub AdvancedFilterTest()
'
' AdvancedFilterTest Macro
'I have tried various ways to create a Variable - as below
'Dim rg As Range
'Set rg = Worksheets("TransTable").Range("B2").CurrentRegion
' but to no avail

'Advanced Filter Recorded Code
Sheets("Reconcile").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
Sheets("TransTable").Range("B2:M172").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("TransTable").Range("U2:U3"), CopyToRange:=Range( _
"B2:M2"), Unique:=False
End Sub

Many thanks in advance.
Mike

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub MikeRetyred()
   Dim rg As Range
   Set rg = Worksheets("TransTable").Range("B2").CurrentRegion
   
   Sheets("Reconcile").Range("B2").CurrentRegion.ClearContents
   rg.AdvancedFilter xlFilterCopy, Sheets("TransTable").Range("U2:U3"), _
      Sheets("Reconcile").Range("B2"), False
End Sub

 

[MikeRetyred]

Gosh Fluff I'm impressed and most grateful.
I'll try it out later this evening (after dinner - i cook as well, and not just The Books !)
MikeRetyred

 

[MikeRetyred]

Hi Fluff,
Again, thanks for your swift response.
I have somehow missed out on transcription - probably a schoolboy error.
In order to understand your solution I created a Sub AdvancedFilterFluff() as follows, adding back some of the extraneous stuff in an attempt to understand better.:
Dim rg As Range
Set rg = Worksheets("TransTable").Range("B2").CurrentRegion

Sheets("Reconcile").Range("B2").CurrentRegion.ClearContents
Sheets("TransTable").Select
rg.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("TransTable").Range("U2:U3"), _
CopyToRange:=Sheets("Reconcile").Range("B2"), Unique:=False

End Sub

Unfortunately the last section fails error code 1004 (with or without the extraneous stuff).
Advice would be appreciated.

 

[Fluff]

What error message do you get?

 

[MikeRetyred]

Apologies
Error Msg as follows:
Error 1004
The extract name has a missing or invalid field name.

 

[Fluff]

No matter what I try I cannot get the code to fail with that error message, so not sure what to suggest.

 

[MikeRetyred]

I really appreciate all your effort on this one. i'll take a look tomorrow and see if I can find anything perhaps by using another test Workbook with the same sheet names. if I find a solution in the next few days - I'll message you
Thanks again,
Mike

 

[MikeRetyred]

Hi Fluff,
My error !
I had an entry in Cell A1, which conflicted with 'Range("B2").CurrentRegion'.
An overnight - Cell A1 deleted - your code worked straight off.
It was a schoolboy error.
Many thanks
Mike

 

[Fluff]

Glad you figured it out & thanks for the feedback