Is there a VBA or Macro solution to make these multiple changes to an Excel file?

Hi all,

I've got an excel file that I need to make a lot of changes to and I need to do this every week so I could do with a macro of some kind to do it all, but I've never used macros or vba before, so I'm not sure where to start. This is everything I need to do.

All this needs to apply to mainfile.csv


1. Remove just the words in brackets (including the brackets)"(*)" from column A

2. Remove just the dash and any words that come after it "- xx" in Column A

3. Remove entire row if "GONE" is in column K

4. Divide value in column X by value in Column V

5. After doing 4 above If value in column X = more than 5 and value in column L = less than 10 (delete entire row)

6. After doing 4 above If column X = more than 10 and value in column L = less than 20 (delete entire row)

7. columm E contains the name of picture files that need to be prepended with MYWORD and appended with MYNUMBER before the .jpg eg. "????.jpg" = MYWORD????MYNUMBER.jpg same needs to be done for .gif files

8. column F contains the name of a text file in each cell (which are located in the same folder as the mainfile.csv file), I need the contents of the text file to replace the text file name. (maintaining formatting as its the synopsis of books)

9. Column B contains a list of 200 different titles repeated randomly 1000's of times. I have a seperate csv file "replacecolumnb.csv" with the 200 titles in column A and the number I want them replaced with in column B. I need to use this file to search and replace the titles in column B in my mainfile.csv

10. Column C contains titles some are prepended by publisher names I have a seperate file with a list of publishers in column A called list.csv If one of the publisher names in list.csv matches the first words of titles in column C in mainfile.csv then I'd like that publisher name to replace what is in column A of mainfile.csv

eg: silent hill in list.csv would match "silent hill tales of life" but not "penguin silent hill" or "silent times" etc

I've been doing most of this work just using excel functions and the rest of it manually which is very time consuming.

Any help with this is greatly appreciated. Thank you

Please attach a sample file.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

These are samples of each file. I've not included the text files. The samples only include 5 rows however the mainfile.csv documents I'll be editing will have over 30k rows. thank you

So there are multiple files this macro should run on? All having the same format? All residing in the same folder?

Do you want the macro to open the 1st file, do the changes, save & close then open the 2nd and repeat, etc?