0

Sorry for my English.

I have a problem with a code where I get information from different sheets to one sheet. With slicer i'm able to only get the necessary information. All worked well till I needed to get a sorting on the data before i copied it. People where using the sheets and filtered and did all sort of things to the list. While I'm writing this I'm thinking maybe i can split it in two because it worked fine before so why not just a code for filtering.. Still I would like to know the solution in my code because I just don't get why it wont work. The problem is (now) in the 2nd part of the code after 'copy paste.

all named references are checked and correct (it worked fine before the filter part) but somehow in the filter part it does something so the code for copy paste wont work.

Option Explicit

Sub GetDataForSlicersSel()
Dim wsAF As Worksheet
Dim wsEM As Worksheet
Dim wsEI As Worksheet
Dim wsOP As Worksheet
Dim wsPG As Worksheet
Dim wsRK As Worksheet
Dim wsKM As Worksheet
Dim wsBK As Worksheet
Dim wsVE As Worksheet
Dim wsRE As Worksheet
Dim wsCP As Worksheet

Set wsAF = Sheets("afblijven")
Set wsEM = Sheets("1721 Energiemeters")
Set wsEI = Sheets("2704 E-installatie OA")
Set wsOP = Sheets("output")
Set wsPG = Sheets("Projectgegevens")
Set wsRK = Sheets("2601 Regelkast-OVK")
Set wsKM = Sheets("2611 Klein Materiaal")
Set wsBK = Sheets("2614 Bekabeling OA")
Set wsVE = Sheets("2621 Veldapparatuur")
Set wsRE = Sheets("2634 Regelinstallatie OA")
Set wsCP = Sheets("1711 Circulatiepompen")

Application.ScreenUpdating = False

'filter
wsEM.Select
wsEM.Sort.SortFields.Clear
Range("EMtabel").Sort key1:=Range("a2"), Header:=xlYes
ActiveSheet.Sort.SortFields.Clear
wsOP.Select

'copy paste
wsEM.Range("EMtabel[#All]").AdvancedFilter _
 Action:=xlFilterCopy, _
 CriteriaRange:=wsAF.Range("Critslicers"), _
 CopyToRange:=wsOP.Range("ExtractSlicersEM"), _
 Unique:=False

Application.ScreenUpdating = True


End Sub
Wilco
  • 1
  • 4
  • 1
    This range is not qualified with a sheet. `Range("EMtabel").Sort` and neither is this one `Key1:=Range("a2")` – braX Sep 30 '21 at 10:20
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Sep 30 '21 at 10:22
  • "The problem is (now) in the 2nd part of the code after 'copy paste'." I don't see any code after advanced filter copy paste - so what exactly is the problem? ... (By the way: you don't need to define all the worksheets in the code - use a codename (in the VBA-Editor properties window for the worksheets) – Ike Sep 30 '21 at 10:26
  • With the `CopyToRange` and `CriteriaRange` you appear to be specifying _Named Ranges_ - do these Named Ranges exist? Try putting the proper Ranges for the sheets in question ("A1:D34" for example). If "Critslicers" is _really_ a variable you've already dimensioned, then remove the "s from around it, or it will literally take the variable as that string. – k1dfr0std Sep 30 '21 at 10:37
  • @Ike the problem is with the code: ``` wsEM.Range("EMtabel[#All]").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=wsAF.Range("Critslicers"), _ CopyToRange:=wsOP.Range("ExtractSlicersEM"), _ Unique:=False Application.ScreenUpdating = True ``` somehow it doesn't work anymore because of the new code i put in front of it ``` wsEM.Select wsEM.Sort.SortFields.Clear Range("EMtabel").Sort key1:=Range("a2"), Header:=xlYes ActiveSheet.Sort.SortFields.Clear wsOP.Select ``` – Wilco Sep 30 '21 at 10:37
  • what do you mean by "somehow it doesn't work" - you have to describe that better: do you get an error, is the wrong range copied, ... ??? – Ike Sep 30 '21 at 10:40
  • I used the code after 'copy paste before and it worked fine. today i added the code after 'filter till the part that worked fine, because i needed some filtering to make sure all data is correct the error i now get is Method range of object_worksheet failed – Wilco Sep 30 '21 at 10:42
  • what if you remove "wsOP.Select"? – Ike Sep 30 '21 at 10:47
  • still same problem. I just don't get it. Trying to make it work on different sheets now. – Wilco Sep 30 '21 at 10:58
  • I feel like such an idiot... code works fine when put on a button just don't use F5 or F8 – Wilco Sep 30 '21 at 11:13
  • can i put this thread on solved myself? Feel like such a noob, so if people read this, try to use a button on one of your sheets – Wilco Sep 30 '21 at 11:14

1 Answers1

0

Solved because of a stupid mistake from my part. This code won't work with F5 or F8 in VBA but will work on a button. Thanks to the advice i also rewritten the code a bit. The code now (i still have the dim and set part but for the lenght of the post i excluded this):

    'filter
With wsEI.Sort
.SortFields.Add Key:=Range("a2"), Order:=xlAscending
.SetRange Range("E_installaties")
.Header = xlYes
.Apply
End With

'copy paste
With wsEI
.Range("E_installaties[#All]").AdvancedFilter _
  Action:=xlFilterCopy, _
  CriteriaRange:=wsAF.Range("Critslicers"), _
  CopyToRange:=wsOP.Range("ExtractSlicersEI"), _
  Unique:=False
End With

Thanks all for helping!

Wilco
  • 1
  • 4