Keeping one country from a sheet and deleting the rest

[mikelowski]

Hi you all,

Every quarter I receive an Excel file with lots of countries and several sheets with different variables being measured for each country company. What I'm supposed to do with it is to create an Excel file for every country. What I'm doing until now is just deleting it manually, which takes a lot of time.

I uploaded a simple example file. First sheet is the original output structure, usually coming with 20-25 sheets measuring different variables from several companies and countries. In the example, for the sake of simplicity, I just put two countries: the UK and France. Second sheet is what I need to produce, keeping only the UK and deleting France. Of course, I also have to do one file with only France.

I hope I've made myself clear so you can help me with this.

Thanks.

 

[Smallman]

Hi Mike

So the only thing to identfy each of the cells as being UK for example can be 1 ,2, n... rows above the actual row the data is contained in. It is very safe to say this is a questionable spreadsheet design. I feel for you if you are getting it from elsewhere.

The best thing to do would be to filter Column D by constants then fill the countries down based on those constants, your data will be tabular (way it should have been in the first place) and identifying countries to keep will be straight forward.

Take care

Smallman

 

[Smallman]

Ok

Here is the coding to achieve what you require. Put all your countries in the Green sheet I have made for you. The procedure will handle all the countries you put in the Green sheet.

The following is the VBA.

Option Explicit

Sub CountryMaker()
Dim lr As Long
Dim ar As Variant
Dim i As Integer

ar = Sheet6.Range("A2", Sheet6.Range("A65536").End(xlUp))
lr = sheet1.Range("E65536").End(xlUp).Row
Sheet1.Range("A1:E" & lr).AutoFilter 5, "=*"
Sheet1.Range("B7:B" & lr).SpecialCells(4).Formula = "=R[-1]C"

    For i = 1 To UBound(ar)
        Sheet1.[a1].AutoFilter
        Sheet1.Range("A1:E" & lr).AutoFilter 2, ar(i, 1), xlOr, "="
        Sheet1.Range("A1:AZ" & lr).Copy
        Worksheets.Add
        [a1].PasteSpecial xlPasteAll
        ActiveSheet.Name = ar(i, 1)
    Next i
End Sub

I will attach a worksheet to show workings.

Take care

Smallman

 

[mikelowski]

Hi, Smallman

Indeed, this data structure is not easy to work with... but I cannot change it for now. Thanks for your help, it's a very interesting approach. What if instead of copying data to another sheet, just remove the not selected countries? Like an autofilter which would apply to every sheet and then delete the rest of entries.

 

[Marc L]

Hi,

you're right ! It's exactly what does my second code in this recent thread !

And you can start your code by using Macro recorder …

___________________________________________________________
Je suis Charlie​

 

[mikelowski]

I tried your second code in the example file of the original poster and I doesn't do anything. Sure I'm running it wrong.

With the macro recorder I tried deleting manually but I end up with a mess.

 

[Marc L]

As you can read, original poster tried with success my second code
and it works too on my side with differents Excel versions !

Maybe you just forgot to check new workbooks created on hard disk ‼

And it's not difficult to progress in code with step by step mode (hit F8 key) …
​

 

[Smallman]

Hi

I do not think you will get what you want from this problem with the recorder. A more elegant solution is required, prefereably one where Variables are declared

Yes you could filter the items in place and remove data from your list. You would want to have some sort of list sheet so you could choose a country to keep and all the potential countries you want removed are listed.

That way you are left with the country chosen. It may be better to set up some sort of master list and have all the names of your tabs in that list then cycle through that list removing the countries not required on each sheet.

The tricky part (which I thought i had addressed) is keeping your sub optimal data structure. One line for headings (not 5). This is the issue.

I have an idea of what it should look like but have some social engagements today so might not get the chance to go at it till much later.

Interesting project though so will try to come up with something out of interest.

Take Care

Smallman

 

[Smallman]

OK

Am back from my social engagements. Actually filtereing for items not in list is a bit of a challenge.

I got this method to work. I am left with a few sheets displaying no doubt unwanted headings. But these can easily be removed with a bit of planning.

The following is the code to remove those unwanted countries. The code assumes you have a sheet for each country in the list. If not you will need to build some error trapping in or provide a workbook which more accurately reflects your actual workbook.

Option Explicit

Sub CountryMaker()
Dim lr As Long
Dim ar As Variant
Dim var As Variant
Dim i As Integer

ar = Sheet6.Range("A2", Sheet6.Range("A65536").End(xlUp))
ar = Application.Transpose(ar)

    For i = 1 To UBound(ar)
        lr = Sheets(ar(i)).Range("E65536").End(xlUp).Row
        Sheets(ar(i)).AutoFilterMode = False
        Sheets(ar(i)).Range("A1:E" & lr).AutoFilter 5, "=*"
        Sheets(ar(i)).Range("B7:B" & lr).SpecialCells(4).Formula = "=R[-1]C"
        Sheets(ar(i)).[a1].AutoFilter
        var = Filter(ar, ar(i), 0)
        Sheets(ar(i)).Range("A1:E" & lr).AutoFilter 2, var, xlFilterValues
        Sheets(ar(i)).Range("A2:E" & lr).EntireRow.Delete
        Sheets(ar(i)).[a1].AutoFilter
    Next i
End Sub

File attached to show workings.

Take care

Smallman