Copying data from a closed workbook into an open workbook ignoring excel filter?

Hi,

Before

Please Login or Register  to view this content.

insert the line

Please Login or Register  to view this content.

Hope this helps

Hi ajryan88, thanks that worked.

I now tried to automate another 2 worksheets from the source file where I wanted to merge the 2 worksheets into the open file

Please Login or Register  to view this content.

What I'm finding is that the header row of the 2nd worksheet is being copied as well (rightly so) and appended along with the data from the first worksheet. How do I delete the header row or not have the header row at all?

ps: Thanks arlu1201, I'll add them from now on.

Cheers,

Reach

No problem!

The simplest option here would be to delete the header row after the paste operation. So after

Please Login or Register  to view this content.

place this line of code:

Please Login or Register  to view this content.

Hope this helps

Thanks ajryan88. however that bit of code deleted the header row of the first table. Here's my illustrated example of what I'm trying to achieve.

macro_table_problem.jpg

Aha whoops, I get it now

Try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hi ajryan88, that works a treat! But I found that for the 'NCD data' code block, the 'AutoFilterMode = False' line does not kick in. It seems to just copy the data that is filtered. That goes for sheets 2,3,4 in the code

Please Login or Register  to view this content.

You've been helping so much. I feel bad for asking more. Good thing is I'm learning but still have not gotten the hang of the syntax and semantics of this language yet...:D

Hi again,

That seems a bit strange that the AutoFilter isn't turned off for sheets 2, 3, and 4 but it works fine for sheet 1.

Would it be at all possible for you to upload your workbooks (both the report workbook and the workbook that you are pasting the data into) so that I can take a look at why this is occurring?

And there's no need to feel bad about it, that's what the forum is here for!

Thanks

Hi ajryan88, thanks for that. I've uploaded the files into google docs.

macro file: https://docs.google.com/file/d/0BxMi...it?usp=sharing
sample table data: https://docs.google.com/file/d/0BxMi...it?usp=sharing

Hi,

I have commented out some of your code and made a few slight changes, but the major change is tagged (with "AJRYAN88") at the beginning of the macro. I have done 3 things:
1. The filters were table filters, not worksheet filters, so slightly different code was required to disable them
2. Rather than opening and closing the workbook to copy each sheet, the workbook is opened at the beginning, all filters are removed, and then workbook is left open until the completion of the macro
3. I have changed the copy mode of sheet 3 from copying the entire sheet to copying only the used range. This is because an error occurs if trying to copy the contents of an entire worksheet onto a cell that isn't A1.

Hope this is working for you now

Hi ajryan88, it's working!

ta mate. Much appreciated.

No problems! Glad I could help

Please don't forget to mark this thread as solved and please click on the * next to my post(s) to say thanks

Reputation added, thanks :D

Hi All,

I think I've gone and killed the code again.

This is for the 'NCD Data' part of the code, what it has gone and done is that it is now not only deleting the header row (which I want deleted) but also the first row of data of the second table I want to append to the first.

Please Login or Register  to view this content.

Also just for my understanding:

Please Login or Register  to view this content.

The code above copies only the 'used' rows and columns of a sheet starting from cell A2 right? Does this mean that for sheets(3) in the original code at the top, it does not copy the header row during the copy process thus eliminating the range for additional coding?

AND

Please Login or Register  to view this content.

The code above, for the current activesheet defines the lastrow and adds 1 row to it (row below the last used row of the first table), then pastes the second table on that row. It then deletes that row (essentially removing the header).

If my logic serves me right, this double elimination has deleted more rows than needed or is my understanding flawed?

I tried changing

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

But it gave me a range class error.

Hi,

Could you please re-upload your workbook with the newest changes in it.

Thanks

Managed to solve it by deleting the last 2 rows of code within the last With...End With block.

Have found another bug. Will raise another query. :D