VBA Code to Copy\Paste Data with out prefiltering from one sheet to another

[Kemidan2014]

Good Afternoon, Long time Reader, first time Poster,
Any who We are wanting to exit using MS Access to maintain a Database of Customer Complaints because 1) the amount of data we need is with in Excels limit 2) we no longer have an Access "Guru" and we can not afford the time to have someone learn it.
I have a master spread sheet that we keep in a safe location that we download and update as we get new complaints
My Excel "data base" is using this master sheet to pull the information so since this file wont be overwritten but the master will be.
For the sake of prebuilding this file it is just called "Sheet1"
I have 22 columns of data that we pull from the master sheet. What I would like to do is tie a button to a macro that will put into "Sheet 2" and when i click it it will Copy and pastespecial as values all rows that contain only Column 4 for a specified value (a supplier code) and Column 6 that does NOT have certain values (this is an overall status, Closed, cancelled, open waiting on what ever step) which has like 15 different states

Idea is this button will be a simple "update" button because the additional sheets will be for us to view which complaints are open while the first sheet maintains rolling history which we do NOT want to filter.

I appreciate any coding help you anyone can share.

 

[DacEasy]

Hi, to accomplish what you described, you could copy the two columns you mentioned to sheet2 and filter it. If you add an update button for the macro, go into the shape properties and select Don't move or size with cells.

Sub forCopyAndFilter()

    Dim wb As Workbook, firstSh As Worksheet, secondSh As Worksheet
    
    Set wb = ThisWorkbook
   
    Set firstSh = wb.Sheets("Sheet1")
    Set secondSh = wb.Sheets("Sheet2")
       
    secondSh.Select
   
    ActiveSheet.Range("B1").AutoFilter
   
    firstSh.Range("D:D").Copy secondSh.Range("B1")
    firstSh.Range("F:F").Copy secondSh.Range("C1")
   
    If Not ActiveSheet.AutoFilterMode Then
        ActiveSheet.Range("B1").AutoFilter
    End If
   
    Rows("1:1").RowHeight = 24.75
    Range("G1").Select
   
End Sub