Question is simple and may be repetitive.
- I have an Excel workbook which contains around 50 columns
- I have a criteria column for splitting this workbook into mulitple workbooks
The approach is as shown below
Name SportGoods quantity
ABC CRICKETBAT 10
DEF BaseballBat 20
GHI football 30
MNO gloves 10
PQR shoes 10
ABCD CRICKET SHOES 10
DEFG BaseballBat 20
GHIL football 30
MNOP gloves 10
PQRS shoes 10
I am looking for a macro which enables me to create multiple Excel workbooks based on the column SportGoods like:
- Excel/CSV for all Cricket Items like CRICKETBAT, CRICKET SHOES, Gloves
- Excel/CSV for all football items like football and shoes
As input parameter I would be providing distinct cricket items, distinct football items.
Source would be a large Excel data sheet which contains ~5000 records.
Can someone help me with a macro which would help in generating multiple workbooks based on the above details?
Best Answer
Summary
This is a short, but smart macro. It splits & saves the data on the active sheet into different CSV files. The newly created files are stored in a new folder called CSV output at the same location as your Excel file.
VBA macro
Save it in a new VBA module
Understanding the code
The first line is your criteria column. A
1
would stand for column A,2
for column B and so on.Second, we define a folder name where all our CSV files should be saved at. You can also set a fully qualified path like
C:\some\folder
. Otherwise Excel will create a folder at your Excel file's locationHere we save our current workbook and worksheet in a variable. Its not neccessarry to do this, but since we are dealing with multiple workbooks (newly created ones) I recommend this
Ok, what does this part? First, we search the last cell only in the criteria column. This must be done before our filtering and is needed later. Then, we use the famous advanced filter method to filter out in place all duplicate values from our criteria column. At last, we save all visible cells in a variable called rngUnique
Lets see if a folder called
CSV output
already exists. If not, create oneNow, we start to loop through all unique values in our variable rngUnique. But empty values are skipped
An important line. We use the autofilter method and view all lines which match our current unique value. The old advanced filter gets canceled automatically.
These two lines create a new empty workbook and copy over only visible cells from our input Workbook
Here we put together the CSV path. We take the current unique value as file name. The extension CSV is appended automatically since we have chosen
xlCSV
as output format.Make sure, your unique values do not contain invalid filename characters like
< > | / * \ ? "
or the corresponding CSV file won't be createdThe last step is to save the current workbook as a CSV and take the variable strFilename as filename. The CSV delimiter depens on your regional setting delimiter. It's possible to change the fileformat, eg. to tab delimited CSV or Excel 2003 workbook
The first line speeds up our macro a bit since Excel doesn't need to show every single step of filtering.
The second line suppresses annoying File already exists prompts. Later we enable those functions again