VBA Copy Visible Rows (and Maybe Columns too) to another workbook

[johnny51981]

I am having difficulty piecing together my VBA Procedure3 to only copy the visible rows that are the result of the filter from Procedure2. The report the code is housed in needs to keep the items that are filtered out, so removing them through my Power Query isn't an option.

Also, I have a helper column that I would like to not be a part of that copy.

How can I update Procedure3 to only copy what is visible?

 

[igold]

A different approach would be to copy the table to the new workbook and then filter it there. You can then copy and paste the filtered data to where you wanted it in the new book. I can't see your data but you could copy the filtered data to anywhere and move it around after that. Three lines of code that may help you with your logic are this:

Dim rng As Range
    ActiveSheet.ListObjects("TASK").Range.AutoFilter field:=15, Criteria1:="<>Quality Audit", Operator:=xlAnd
    Set rng = Worksheets("TASK DATA").AutoFilter.Range
    rng.Copy Range("U1")

I would use this after you copy the unfiltered table to the new book. I just indiscriminately picked Cell U1 to paste the filtered data to, but you can figure out your own parameters based on your needs.

 

[johnny51981]

I appreciate your response, @igold but I am in need of the Quality Audits to not be moved over to the csv it is directed to.

 

[igold]

I used your filter and the data that is copied & pasted does not contain any rows with Quality Audits.

 

[johnny51981]

Apologies, @igold . I'm a super novice within VBA and I read your response as the code needing to be in the landing csv workbook.

Would you be able to help me a little bit further to show where this 3 line code that you provided would be placed within my existing?

 

[igold]

I am more than happy to help but I need a little more info...

Does the Worksheet "TASK DATA" contain any other data other than the Table "Task"?
In the CSV you are creating do you care what cells (or where in the Workbook) contain the filtered data?

 

[johnny51981]

"TASK DATA" is just table "TASK".
The Active Tasks.csv has a single worksheet labeled "Active Tasks" starting in cell A1.

 

[igold]

Last question: In your original workbook what column letters does the TASK table occupy.

 

[johnny51981]

It occupies A through AY, however I would also like to exclude column A as it is a TRUE/FALSE function based on row height, and not needed in the CSV if possible.

 

[igold]

I took a different route than I first suggested but this should fulfill your requirements. I would replace both your subs Procedure2 & Procedure3 with this code. Don't forget to change your first sub "RunAllMacros" to reflect this change...

Sub Procedures2and3()

    Dim rng As Range
    
    ChDir ThisWorkbook.Path
    ThisWorkbook.Sheets("TASK DATA").Copy
    ActiveSheet.ListObjects("TASK").Range.AutoFilter field:=15, Criteria1:="<>Quality Audit", Operator:=xlAnd
    Set rng = ActiveSheet.ListObjects(1).Range.SpecialCells(xlCellTypeVisible)
    Range("A:AY").EntireColumn.Insert
    rng.Copy Range("A1")
    Sheets("TASK DATA").ListObjects(1).Delete
    Range("A1").EntireColumn.Delete
    
    ActiveWorkbook.SaveAs fileName:="Active Tasks.CSV", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    
    ThisWorkbook.Sheets("GOAL ECCD").Copy
    ActiveWorkbook.SaveAs fileName:="Goal ECCD by Job.CSV", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    
End Sub

I hope this helps...