Hi everyone,
I have a spreadsheet that retrieves data from various files and I input a column header via array.
This is not the problem. The error happens when I used the Advanced Filter copyto method. I have a couple other spreadsheets where I copy from and it contains more headers but I only require the ones listed above.
Spreadsheet 1
Spreadsheet 2
Spreadsheet 1 and 2 you can see are pivot tables. There is another tab which gets filled with people's requests and the pivot tables will update. These spreadsheets are used to tracker our workers "Time Off" approved requests and it contains 10 different types but I only need the 3 listed above in my array. As you can see on "Tracker_AM" the header contains all 3 of the time off options while "Tracker_NZ" does not, which means people with names start with N through Z did not request any Medical Waiver time off.
Where my problem is that since I hard coded the header with my array, it throws an error and stops. Is there any way to do this even the header from my source range does not contain all the header from my criteria range?
I have a spreadsheet that retrieves data from various files and I input a column header via array.
VBA Code:
ary = Array("Row Labels", "Bereavement", "Medical Waiver", "Provincial Leave")
sht.Range("A1:D1").Value = aryThis is not the problem. The error happens when I used the Advanced Filter copyto method. I have a couple other spreadsheets where I copy from and it contains more headers but I only require the ones listed above.
Spreadsheet 1
| YYZ4_Provincial_Tracker_AM.xlsb | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Min of Balance | Column Labels | ||||
| 2 | Row Labels | Bereavement | Medical Waiver | Provincial Leave | ||
| 3 | test 1 | Not Used | Not Used | 7 | ||
| 4 | test 2 | Not Used | Not Used | 9 | ||
Balances | ||||||
Spreadsheet 2
| YYZ4_Provincial_Tracker_NZ.xlsb | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Min of Balance | Column Labels | |||
| 2 | Row Labels | Bereavement | Provincial Leave | ||
| 3 | test 3 | Not Used | 9 | ||
| 4 | test 4 | Not Used | 8 | ||
Balances | |||||
Spreadsheet 1 and 2 you can see are pivot tables. There is another tab which gets filled with people's requests and the pivot tables will update. These spreadsheets are used to tracker our workers "Time Off" approved requests and it contains 10 different types but I only need the 3 listed above in my array. As you can see on "Tracker_AM" the header contains all 3 of the time off options while "Tracker_NZ" does not, which means people with names start with N through Z did not request any Medical Waiver time off.
Where my problem is that since I hard coded the header with my array, it throws an error and stops. Is there any way to do this even the header from my source range does not contain all the header from my criteria range?
