Macro to copy a Workbook, only keeping specific data

RichJW

Board Regular
Joined
Jul 7, 2015
Messages
86
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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?
 

RichJW

Board Regular
Joined
Jul 7, 2015
Messages
86
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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.
 

RichJW

Board Regular
Joined
Jul 7, 2015
Messages
86
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!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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?
 

RichJW

Board Regular
Joined
Jul 7, 2015
Messages
86
That would be fantastic, thank you mumps.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
Do you mean that you want just with "Apple" or to be prompted?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,506
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
 

Forum statistics

Threads
1,081,574
Messages
5,359,707
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top