Solved VB Macro for Excel Create New Files

September 12, 2019 at 12:59:08
Specs: Windows 10
I have an excel file with bunch of countries. I'd like to sort the file by country, and then take each data set and copy it into a new file, saved as the name of the country name.

For example, if my original file has Mexico, Chile and Argentina, I'd run the VB script and it would take all lines that have Mexico in column B, copy those lines into a new file and save the new file as Mexico.xls, it would do the same for all the rest of the countries.

I'm not familiar w/ VB at all, so not certain where to begin.


See More: VB Macro for Excel Create New Files

Reply ↓  Report •

#1
September 13, 2019 at 21:21:13
✔ Best Answer
First, let's make sure that we use the correct terminology.

1 - A "macro" and a "VB Script" are not the same thing. A macro is written in VBA (Visual Basic For Applications) and a VB Script is written in Visual Basic. I'll leave it up to you to research the difference.

2 - re: "I have an excel file with bunch of countries"

I assume you mean that you have an Excel sheet with a bunch of countries. A "file" would be the Workbook itself, that which you Open, Save etc. You can't sort a "file", copy rows in a "file", etc. You do those things in a sheet or sheets within the Workbook.

3 - re: "it would take all lines that have"

By "lines" I assume you mean Rows. An Excel sheet has Rows and Columns, a text document has "lines".

That said, this code should get you what you asked for.

I had to make a few assumptions.

1 - The existing sheet that contains all of the countries is the first sheet in the workbook.
2 - That sheet has Column headers in Row 1 that you want to copy into each country-specific sheet
3 - You want the country-specific sheets saved with a .xls filetype

Note: This macro does not sort the existing sheet. It is not necessary to sort the sheet before creating all the country-specific sheets. If you want the sheet sorted for some other reason, we could certainly add code to do that. However, if you asked that sheet be sorted by country because you thought it needed to be sorted in order to create the other sheets, we can skip that step.

Let me know what you think.

Sub CountrySplit()

    Dim V As Variant, COL As Collection
    Dim I As Long
    Dim vUniques() As Variant


'Copy all countries into variant array
'  This will execute significantly faster than reading directly
'  from the Worksheet range

 With Worksheets(1)
    V = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
 End With

'Collect unique values
'Use the key property of the collection object to
'  ensure no duplicates are collected
'  (Trying to assign the same key to two items fails with an error
'  which we ignore)
 Set COL = New Collection
 On Error Resume Next
  For I = 1 To UBound(V, 1)
    COL.Add Item:=V(I, 1), Key:=CStr(V(I, 1))
  Next I
 On Error GoTo 0

'Write collection to variant array
ReDim vUniques(1 To COL.Count)
  For I = 1 To COL.Count
    vUniques(I) = COL(I)
  Next

'Loop through array of unique countries

 For I = 1 To COL.Count
 
'Add sheet for current country, copy header row

   Sheets.Add(After:=Sheets(Sheets.Count)).Name = vUniques(I)
   Sheets(1).Rows(1).EntireRow.Copy Sheets(vUniques(I)).Cells(1, 1)

'Find/Copy each row that contains current country

    With Sheets(1).Columns(2)
     Set c = .Find(vUniques(I), lookat:=xlWhole)
      If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            nxtRw = Sheets(vUniques(I)).Cells(Rows.Count, "B").End(xlUp).Row + 1
            c.EntireRow.Copy Sheets(vUniques(I)).Cells(nxtRw, "A")
          Set c = .FindNext(c)
        If c Is Nothing Then
            GoTo DoneFinding
        End If
        Loop While c.Address <> firstAddress
      End If
DoneFinding:
    End With

'Save the sheet
   Sheets(vUniques(I)).SaveAs Filename:=vUniques(I) & ".xls"

 Next I
 
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
September 17, 2019 at 11:18:31
Thank you very much for the answer, and apologies for using the wrong terminology, but all your assumptions/corrections are accurate. I'll test the script and see how far it gets me.

The file is already sorted, and it's easier for me to sort it manually than add lines.

Thanks again for the answer!!!


Reply ↓  Report •

#3
September 17, 2019 at 11:57:04
re: "...and it's easier for me to sort it manually than add lines."

I'm not quite sure what you mean by "add lines" so I want to make sure that you understood what I was saying:

There is no need to sort the sheet for the code work. It will work whether the sheet is sorted or not.

If you want to sort the sheet for your own reasons, feel free, just don't do it because you think you have to in order for the code to work. The code doesn't care if it's sorted or not.

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
October 10, 2019 at 14:05:29
Turns out the Country column is actually column C. I'm trying to update the macro by changing the following line:
V = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With, I change B to C

and also:
nxtRw = Sheets(vUniques(I)).Cells(Rows.Count, "B").End(xlUp).Row + 1
c.EntireRow.Copy Sheets(vUniques(I)).Cells(nxtRw, "A")
Set c = .FindNext(c), I change the Rows.Count,"B" to C

This does not work and although it creates sheets/new files, all are blank. I'm assuming this would be a quick change, but I'm struggling to make the change.

Thanks in advance


Reply ↓  Report •

#5
October 12, 2019 at 06:40:42
I haven't been ignoring you, I've just been extremely busy. I'll try to take a look at your issue this weekend.

Sorry for the delay.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
October 14, 2019 at 12:14:00
Try also changing this line:

    With Sheets(1).Columns(2)

to

    With Sheets(1).Columns(3)

Columns(2) is B, Columns(3) is C.

In the future, please use the pre tags when posting VBA code and VBA instructions. It makes the code easier for us to read. Click the following link for more information.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
October 17, 2019 at 13:24:45
Thanks Derby! Really appreciate your help with this!

Reply ↓  Report •

Ask Question