How to Copy Filtered Data From Source Worksheet to Target Worksheet?

I have data on a worksheet that I need to filter in a number of different ways, and then for each filter setting I want to copy the range of visible cells to another worksheet - Consolidated_Data - however despite hours of trying I'm unable to make it do what I want.

I currently use:

ActiveSheet.Range("E16:P4000").Copy Destination:=Worksheets("Temporary Data").Range("E16")

to copy unfiltered data between two other sheets, and that works fine so I thought that maybe I could use a similar formula to copy the filtered data, however when I used:

ActiveSheet.Range("E16:DK4000").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Consolidated_Data").Range("A16")

I got an Error pop-up simply showing 400.

With only 1 line of code it's not hard to know which line is causing the problem, and it's seemingly because of the SpecialCells that I added in, so how do I correctly make it copy and paste the visible data?

Once I get the first block copied and pasted, I then want to reset the filter to a new set of conditions and then copy the new block of visible data to Consolidated_Data immediately below the first block. How do I incorporate this into the coding?

Hi graha_karya

Sorry - it was a long and tiring day yesterday so maybe I was too brief and didn't fully explain myself.

I have a worksheet with a mass of data that I can filter to show me the outcome from different parts of the process, but I have no way of seeing the the outcome from all the filters at the same time. The only way that I can think of, to enable me to see the total outcome, is to apply a filter and copy the visible cells to a sheet that I have called 'Consolidated_Data', and repeat this process for each filter.

So, for example, the first filter shows me:
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Filt01.png","data-attachmentid":1210741}[/ATTACH]
I have a Command Button - "Copy Cells to Cons_Data" - which I would hit and it would then copy that block of cells to 'Consolidated_Data'.

Then I change the filter settings and the new filter outcome is:
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Filt02.png","data-attachmentid":1210742}[/ATTACH]
I would hit the "Copy Cells to Cons_Data" button and the new block of cells would be copied to 'Consolidated_Data', beneath the first block of cells.

I reset the filter to a different part of the process and obtain a new filtered outcome (I can't show you a picture because we're limited to 2 x images).
I would hit the "Copy Cells to Cons_Data" button and the new block of cells would be copied to 'Consolidated_Data', beneath the second block of cells.

I keep repeating that process until I've applied each filter that I want to use, and on the 'Consolidated_Data' sheet I then have the combined output from all the filters. I can't show you a picture of what that would look like, firstly because I don't know how to code it to make it copy those visible cells from the source sheet to the 'Consolidated_Data' target sheet, and secondly because of the 2 x images limitation.

I already use existing coding that allows me to copy part of the unfiltered worksheet to a temporary back-up:

Sub BackUp()
     ActiveSheet.Range("E16:P4000").Copy Destination:=Worksheets("Temporary Data").Range("E16")
End Sub

I thought that by using similar coding, maybe I could copy the visible cells to another sheet, so I tried:

Sub CopyVisibleRange()
    ActiveSheet.Range("A16:DK4000").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Consolidated_Data").Range("A16")
End Sub

but it resulted in an error pop-up that just showed "400". I then tried adding Paste and PasteSpecial and several other things but I'm unable to make it copy the visible cells to the target sheet. I've scoured the internet to try to work out how to do it but I'm at a complete loss - hence my call for help.

Trust that clarifies it.

After a few more hours of tearing my hair out I've made a bit of progress.

The '400' error pop-up was because measurements are taken twice daily so on the source worksheet the date was merged over two rows for each day. The merging has been eliminated, the date now shows on every row and the '400' error pop-up no longer occurs.

As a result I can successfully now copy filtered data to the summary sheet using:

Sub CopyVisibleRange()


    Range("A16:DK4000").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Consolidated_Data").Range("A16").PasteSpecial (xlPasteValues)
       
End Sub

The area where I still require help is to amend that coding so that instead of being copied to a specific cell address it is copied to the first blank cell in col A below the existing data. I've been experimenting with End(xlUp) and End(xlDown) and Rows.Count and more but so far I've not been able to make it work.

Assistance with this will be much appreciated.