two subs that work seperately, but not together

[Zanthus]

Hi There. I have two subs which i've got working independently, but when I try and call the alrger sub from the more basic one, i get the error "Argument not optional". I thought at first it was because i was declaring the same variable (Target), but i've changed this so one sub uses Target1 and one just Target, but to no noticeable change. To make it clear what is happening. I have created a data validation list, which a user can select from. This list populates a cell (AP10) and I then am trying to have a pivot table, update based on that cell. AP11 is also a filter I want to apply. I have a large chunk of data that the pivot table points to, with codes for a series of data types, and then data subsets (all 3 character).

Any help or advice would be greatly appreciated.

Please Login or Register  to view this content.

[humdingaling]

please use code tags for all your coding

forum rule #3
http://www.excelforum.com/forum-rule...rum-rules.html

i see the potential issue but can reply after you have changed your post to use include the code tags

[Zanthus]

Doneski . I don't do excel code often, and usually i can get things to work, which means I ask questions even less frequently :/

[humdingaling]

what i think your missing is the byval in your call code

Please Login or Register  to view this content.

[Zanthus]

I had the byval in initially but it didn't work, so removed it. Unfortunately, that doesn't solve the problem.

[humdingaling]

you have a 3rd sub filter_pivotfield?
is that causing the error? or is the error still between worksheet change and update main pivot?

very hard to determine issue without the file itself
are you able to attach sample file?

[Zanthus]

I think theres a real chance i'm overcomplicating this. I can't share the actual document because the information is sensitive, but i can eqasily sumarise what it looks like.

In sheet2, i have a bunch of data. it falls into 12 "areas" identified by a 3 character code (aaa, bbb, ccc etc) - there are 10 possible area codes. Further to this, each of the lines of data has a "type" code of 3 characters - there are 3 possible type codes (abc, abd, abe etc.). Within each of these rows of data, certain cells are populated based on the type. So each area row entry has data in at least 4 columns, but possibly 6, and these columns change (specifically) based on the type.

What I'm trying to do is copy and paste the matches for each combination. So when a user selects "aaa" and "abc" then copy all rows that match both those criteria, but only columns F,G,H and J. Or if its "aaa" and "abd" then copy each matching row but columns "R,S,T,U,V" etc

Does this make sense?

I can probably make a dummy set of data up if that hasnt helped.

Appreciate the assitance.

[mikerickson]

I note that the argument in the declarationg of the Change Event is named Target1. Normally the argument is named Target (no one). This might be causing the problem.

[humdingaling]

sorry cant see it
but it sounds like all you are trying to do is automate pivot table based on DV values

instead of using DV have you thought about using slicer instead?
http://www.contextures.com/excelpivottableslicers.html

[Zanthus]

The slicer function acheives the selection of the two selectables, but, it doesnt allow me to dynamically change the column headers and spacing. I've done what im trying to achieve previously and i can re-use that, but it is extraordinarily inelegant and uses 10 x sheet tabs with offset data and just copy-paste code. I'm sure what im looking at can also be done using loops to find values and copy-paste

[Zanthus]

I've found and modified some code that _ALMOST_ does what i want it to, without using pivots. The code below is copying the matched data (correct) to another sheet. The problem is I want to copy more than one cell of data for each matched row, but not the entire row. I can't figure out how to weave that into this code.

Please Login or Register  to view this content.

[protonLeah]

You have: MyArr = Array(Sheets("Sheet6").Range("D4").Value)
But one cell is not an array and the lower and upper bounds are both zero; your loop iterates only once.
Then, you are using .Find to locate the value in column F; but, you need some kind of IF condition or lookup table which will tell you which columns to copy based on the found value.

[Zanthus]

Yep, I realise that protonLeah, I'm not sure how to create that table in VBA. the number of cells to be copied in each found row, is based on a set criteria. Whilst there are multiple combinations of criteria, there are only 3 identifiers that impact cell copy size.

Does the actual look of what im messing with help? I've created the dummy data attached.samplesheet1.xlsx

Sorry if I'm coming accross like a dumbass here :/

[protonLeah]

Something like:

Please Login or Register  to view this content.

[Zanthus]

This just copies the cells in the search range, Column F, into the new sheet where they match the contents of D4. I was able to do that without the code addition. I've dummy data running multiple columns over from the range search, so well within the range of the offset parameters.

[Zanthus]

I've just managed to solve it using a nested Index Match as an aray formula! Appreciate your time trying to help though