Filter range and copy to another worksheet

[robertmwaring2]

Hello all,

I have been working on a project for some time now. It is bascially finished - thanks to much help from you all, and the macro recorder function in excel.

I used that function to come up with the following code which selects a column of data that contains blanks intermitently throughout the data, filters out the blanks, and copies it to another worksheet in the same workbook.
This happens four times and use the same code for each instance changing the range references, of course. It does, however, seem to take a bit of time to run - and depending on the machine the workbook is run from, it can be quite long I'm noticing. Is there a way to possibly stremline it so it runs a bit smoother? Or do I just need to suck it up and deal with it.

---

Sheets("Info Sheet").Activate
Sheets("Info Sheet").Range("AKW1:AKW756").Select
Selection.AutoFilter
Sheets("Info Sheet").Range("$AKW$1:$AKW$756").AutoFilter Field:=1, Criteria1:="<>"
Sheets("Info Sheet").Range("AKW1:AKW756").Select
Selection.Copy
Sheets("Production Sheet").Range("G8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Info Sheet").Range("$AKW$1:$AKW$756").AutoFilter Field:=1
Application.CutCopyMode = False
Selection.AutoFilter

---

Some details that may help:

Workbook Name : Master Production Sheet
Sheet 1 Name (Hidden) : Info Sheet
Sheet 2 Name (Visible) : Welcome
Sheet 3 Name (Very Hidden) : Production Sheet

The above code is set in a Module <Module 1> that runs when a button on a UserForm <UFProductionSheet> is pressed. The Userform is accessed Sheet 2 ("Welcome") as all others are hidden.

Thanks in advance for any assistance - I truly appreciate it

Robert

 

[SpillerBD]

AutoFilter...?
Check out using Advanced Filter using the copy to destination option.

 

[MARK858]

Doing away with l those activates and selects should speed up the code a bit

    Application.ScreenUpdating = False
    
    With Sheets("Info Sheet").Range("$AKW$1:$AKW$756")
        .AutoFilter Field:=1, Criteria1:="<>"
        .Copy
    Sheets("Production Sheet").Range("G8").PasteSpecial Paste:=xlPasteValues
        .AutoFilter
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

Btw, I removed the last Info Sheet autofilter as you have no criteria so I don't know what you were trying to do with it.

 

[robertmwaring2]

@MARK858
Thank you kindly. I'm still learning and I expect I will always be as it appears there is so much this can do. This was all basically like reading chinese to me in the beginning. While your code makes perfect sence to me when I read it now, I still don't have the knowledge to think that way yet. In any case, I appreciate the assistance. I've been spending the day researching and trying to streamline much of what I spent weeks doing. I find I rather enjot this, lol.
Thanks again.