Code doesn't copy the data without any errors

Hi, I hope someone can point out to me what I am doing wrong. I have the following code, that works without error except for the red part. It doesn't copy the data over and no error is displayed. What is going on?

Please Login or Register  to view this content.

Attach the workbook, please.

I'm sorry, but I cannot attach none of them due to the fact that both contain a lot of sensitive data

Then attach a desensitised version that displays the issue.

Comment out the On Error Resume Next line, which is why you aren't seeing any errors, then rerun the code and tell us the error message. At a guess I'd say the pivot table names are wrong, unless you manually added a full stop to them.

rorya, you are correct. I actually changed the pivot tables names and I forgot about it. Now it works, but the strangest part is that is copying only the data for pivot4, and not for the rest of them

Is that with the On error commented out?

Yes. only the last pivot data gets copied and pasted. All of them have the same format, 2 filters and 1 header line. the rows are different, but shouldn't be a problem. My guess is that it has something to do with the Offset but I don't know

If you don't want the filter fields, you'd be better off using Tablerange1 as a starting point:

Please Login or Register  to view this content.

I'd probably also refactor this part as you're basically doing the same operations each time.

rorya, you are absolutely amazing. many thanks for it. solved my issue

Glad to help.

Here's a refactored version. Add this routine:

Please Login or Register  to view this content.

then the code I posted earlier just becomes:

Please Login or Register  to view this content.

This is even better rorya. I have another question. How can I paste a pivot as values, then define the lastrow in the worksheet in which it was pasted and paste a second one 3 rows below it?

If there's a column that will always be populated for every row of the pasted data, you can use that to find the last populated row and then offset by 3.

Taught so, so I wrote it like this:

Please Login or Register  to view this content.

But the line in red gives me an error. If I add B:B instead it will populate the rows starting in B1 unstead as I have blanks between B1 and B8. Any ideas why?

You've already found the cell to paste to, so just use it:

Please Login or Register  to view this content.

That fixed the error, but it doesn't copy the pivot table which is really weird

If you're not getting an error, it must be going somewhere - I'd assume to whatever sheet is active at the time. This part:

Please Login or Register  to view this content.

should read:

Please Login or Register  to view this content.

{the red part is the most important change)

you sir, are amazing! many thanks for everything

Glad to help.