problem with advanced filter in excel vba again

[xq]

thanks Dale Watson again, ur file is really helpful.
but i have another problem and this's kind of weird for me.

Sub Extract()
dim r2 as Integer
r2 = 200
Application.ScreenUpdating = False
worksheet("sht1&quot.Range("E2:E"& r2).AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=worksheet("sht2&quot.Range("A2:A10&quot, _
Unique:=True
Application.ScreenUpdating = True
End Sub

if i use worksheet buttom to run this macro, it works perfectly, copy all unique item from range in worksheet 'sht' to range in worksehet 'sht2'. but if i call this macro from the other macro and pass the value r2 which is 200 from that macro to this 'Extract' macro, it won't work, the code is:

Sub Extract(r2 as integer)
Application.ScreenUpdating = False
worksheet("sht1&quot.Range("E2:E"& r2).AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=worksheet("sht2&quot.Range("A2:A10&quot, _
Unique:=True
Application.ScreenUpdating = True
End Sub

it will produce nothing, and there's no error message. why?
thank u for any help!

 

[DaleWatson123321]

Hi xq,

I've adjusted your macros, and they now work.

From making the adjustments, I noticed that even the macro that you say works, did NOT work for me. It was because you were missing the "_" character at the end of the following line...

worksheet("sht1&quot.Range("E2:E"& r2).AdvancedFilter

Plus, there were other omissions - e.g. an "s" on worksheet. - i.e. it should be "Worksheets".

The main realization I've made here, is that it appears you have RE-TYPED the VBA code. Had you copied the code, "worksheet" would also have started with a CAPITAL W - i.e. Worksheets.

INSTEAD, in future, you should know that the code can be COPIED. ...which of course will make it EASIER for you, but ALSO "ACCURATE".

Also, for any code supplied in Tek-Tips, you can copy and paste it directly from Tek-Tips into your VBA Module.

Back to your problem...

After making the necessary corrections, the following routines now work without any problem.

Dim r2 As Integer

Sub Other_Routine()
r2 = 200
Extract_2
End Sub

Sub Extract()
Dim r2 As Integer
r2 = 200
Application.ScreenUpdating = False
Worksheets("sht1&quot.Range("E2:E" & r2).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Worksheets("sht2&quot.Range("A2:A10&quot, _
Unique:=True
Application.ScreenUpdating = True
End Sub

Sub Extract_2()
Application.ScreenUpdating = False
Worksheets("sht1&quot.Range("E2:E" & r2).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Worksheets("sht2&quot.Range("A2:A10&quot, _
Unique:=True
Application.ScreenUpdating = True
End Sub

I hope this helps. Please advise as to how you make out.

The final suggestion I would make, is for you to take a look at the example files I sent you - regarding how I use "range names" whenever possible.

Some of the examples should show how I re-adjust the coordinates of these named ranges via code.

The advantage of using the range names, along with the "automatic" method of adjusting these names - to allow for potential addition of rows or columns to the applications, is that you will then NOT have to be in the position of having to continually modify your VBA code whenever adjustments are made to the application.

These &quot;range names&quot; can be a REAL benefit - because internally Excel maintains the &quot;whereabouts&quot; of these names. Therefore, whenever columns or rows are inserted or deleted, or data is moved, Excel automatically adjusts the coordinates of these range names. These names can also be a benefit when creating formulas, or when navigating - i.e. you can simply hit the &quot;GoTo&quot; key <F5>, type the name, and <Enter>.

In case I missed it in the example files, the method of creating a range name is simple...

1) Highlight the cell or range-of-cells
2) Hold down the <Contro> key, and hit <F3>
3) Type the name
4) Hit <Enter>

Avoid using names that will potentially conflict with Excel's cell coordinates or with VBA commands. For example, don't use a name like &quot;C9&quot;, instead use &quot;C_9&quot; or &quot;_C9&quot;. Instead of &quot;Range&quot;, used &quot;Range_1&quot; When in doubt, using the &quot;_&quot; character always makes it safe.

Regards, ...Dale

 

[DaleWatson123321]

Hi xq,

I'm curious to know how you made out on this ???

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xq]

thanks for ur concern, well, actually the problem is not really about advanced filter, advanced filter itself is working perfectly, cos i'm querying data from sql server, it takes time to let result appear on the worksheet, and it only appears when all the macros r finished, include those macros should run based on those data. therefore i adjust my code as putting a timer, and use seperate command buttom to run the rest code. (wonn't work on running all the macros by one command buttom even with a timer.)
that's how i solve it, not very good, but the only way i can think.