Macro to copy a Workbook, only keeping specific data

RichJW

Board Regular
Joined
Jul 7, 2015
Messages
94
Hi,

I have an (O365) Excel report, with two tabs, which is my master version.
I need a macro that will do the following for me, as I need to produce several reports from this master version.

1. Copy the master version.
2. Only keep the rows of data where in column D it says "Apples" in both tabs (header is on row 4 on each tab, so data commences on row 5 and the number of rows with data changes weekly). So, for anything saying "Pears" or anything else in column D, the row gets deleted.
3. Name the file "Apples Report", so basically whatever filter I am looking at.

I can then just duplicate this macro for my other categories.
I need to do this for about 5 reports, so it would help me greatly.

Thanks,
Rich
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
How do you determine the criterium to use for the filter ("Apples")? Do you want to be prompted to enter that criterium. What is the full path to the folder where you want to save the new file?
 
Upvote 0
Hi mumps, the criteria is quite random. Out of about 8 varying options in column D, I need to split 4 of them out, however another 4 need to be kept together. I don't wish to be prompted, however providing I can select more than one entry, then that would work. I would wish to save the file in the existing folder, so C\users\Me\OneDrive-Me\Reports.

Thanks
 
Upvote 0
I need to split 4 of them out, however another 4 need to be kept together
Are you saying that you want to delete the rows based on 4 values in column D and keep the rest? If so, will it always be 4 values? Please clarify.
 
Upvote 0
Not always, but for the time being it is. I hoped that in the macro it would state, for example "Apples" and then if I had another one added, I could just duplicate the macro and change the name. Is that not how it would work? Sorry if I'm not answering your questions well!
 
Upvote 0
I can suggest something that will work with "Apple" if that's what you want, but if it's always only one criterium and that criterium varies, the macro can simply prompt you to enter it and then it would do the rest based on you input. This way you don't have to duplicate the macro. Just to clarify, will you be using only one criterium or more than one at the same time to filter the data?
 
Upvote 0
Do you mean that you want just with "Apple" or to be prompted?
 
Upvote 0
Try:
VBA Code:
Option Compare Text
Sub FilterData()
    Application.ScreenUpdating = False
    Dim lastRow As Long, ws As Worksheet, srcWB As Workbook, response As String
    response = InputBox("Enter the filter criterium.")
    If response = "" Then Exit Sub
    Set srcWB = ThisWorkbook
    Application.Workbooks.Add 1
    For Each ws In srcWB.Sheets
        ws.Copy Sheets(Sheets.Count)
    Next ws
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
    For Each ws In Sheets
        lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With ws.Cells(4, 1).CurrentRegion
            .AutoFilter 4, "<>" & response
            ws.Range("A5:A" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            ws.Range("A1").AutoFilter
        End With
    Next ws
    ActiveWorkbook.SaveAs Filename:="C:\users\Me\OneDrive-Me\Reports\" & response & " Report.xlsx", FileFormat:=51
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top