Extend copy range of a macro in a query file

[Consty1]

In the sample sheet attached I have 2 query files, one immediately follows the other and they are both the same file and contain the same data. I have 2 macros set up to transfer data to sheet2. The HomeCopy macro transfers data from the first query table and the AwayCopy macro transfers data from the second (orange) query table. The code I'm using for the HomeCopy macro is:

Please Login or Register  to view this content.

The issue I'm having is that each query table would be updated weekly and the amount of scores increased so the range in the macro "A2:U16" would no longer applicable. This range is always the total amount of rows in the query table so for example if I added 4 new scores to the query file then the required range would be "A2:U20". Is it possible to set the range as the total amount of rows in the query file so that when it was refreshed each week the macro would pick up the newly added data? I may have explained that badly, not sure.

Thanks in advance for any replies. Sample 14 .xlsm

[xladept]

Try:

Please Login or Register  to view this content.

[Consty1]

Hi, thanks for your suggestion. This is copying all the visible data for BOTH query tables. I'm looking to copy the visible range of just the blue query table using the HomeCopy macro. The AwayCopy macro would then copy the visible data of the orange query table.

[xladept]

Your sample doesn't show me blue or orange and it only labels "Home"???

[Consty1]

Ah ok, you don't see this?

Sample 14 SS.PNG

[xladept]

Hi Consty,

I'm sorry - I first looked at this on my PC which only runs 2003 - when I downloaded to my 2010 - I got the colors. Where are you pasting the away?

Try this:

Please Login or Register  to view this content.

[Consty1]

The home macro is separate from the away macro and they would never be used together. Essentially the away macro should mirror that of the home macro and would be placed into the same destination cell on sheet2 when ran. I've tried the above code and just removed the away information but have encountered a couple of issues.

1. The header for the blue query table is being copied onto sheet2. This isn't required.
2. If I filter a column in the blue query table and run the macro, all of the data is being copied to sheet2 and not just the filtered visible data.

The functionality of the code in my original post is fine, it's just extending the range when I update and refresh the query file which is the issue.

[Consty1]

The home macro is separate from the away macro and they would never be used together. Essentially the away macro should mirror that of the home macro and would be placed into the same destination cell on sheet2 when ran. I've tried the above code and just removed the away information but have encountered a couple of issues.

1. The header for the blue query table is being copied onto sheet2. This isn't required.
2. If I filter a column in the blue query table and run the macro, all of the data is being copied to sheet2 and not just the filtered visible data.

The functionality of the code in my original post is fine, it's just extending the range when I update and refresh the query file which is the issue.

[xladept]

Well, if your code works with the filtered data then this may solve your problem:

Please Login or Register  to view this content.

[Consty1]

That's it! Thank you, really appreciate you taking a look at that.

[xladept]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

And, You're welcome