Filtering an Existing XLS file

Hi Guys. I've been working for a very long while on a way to automatically filter a report using VBA.
Problem is, I don't know VBA at all, and I use the a recorded Macro, the problem is, the recorded Macro looks on Column References and no Column names, the same for Rows, so if the colon order is changed or the number of ROWS / Columns changes, I have to re-record the Macro every time - which isn't a solution.

This is not going to be simple so I thank in advance for everyone who will take the time to help me!

The RAW report I get is an output from RVTools which gives out A LOT of columns (but as we sometimes add or remove custom annotations, the number of columns may change)
What I need the script to do, is to open a new excel file with the name Filtered.xlsx and save it under \\network\path
then from the same location, open an existing file called raw.xls
Then, from RAW file, copy only the following columns:
VM, Powerstate, CPUs, Memory, Provisioned MB, In Use MB and OS into the Filtered File, but only copy the rows where the Clientname is 'Webox'
Then, Rename the Memory, Provisioned MB and In Use MB columns to RAM. Provisioned GB and Used GB respectively and divide the values of said columns by 1024. and then format it as a Table (Style Medium 9)
LVL 9
David SankovskySenior SysAdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ElrondCTCommented:
If you have a macro that works now (with the current spreadsheet structure), it's probably easier to tweak that than to write everything from scratch. A couple of items that I can immediately suggest:

* To get the total number of rows in a spreadsheet:
    ActiveCell.SpecialCells(xlLastCell).Select
    RowEnd = Selection.Row
 

Open in new window


* To find the column a particular title is in:
    Range("A1").Select
    Cells.Find(What:="This Column Title", after:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Activate
    ThisCol = Selection.Column

Open in new window


If you'd like help putting this or other code into what you got by recording a macro, how about posting the macro, either as a code block in a message or as an attached file? Thanks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David SankovskySenior SysAdminAuthor Commented:
This might have to wait until I'm at work again.
I'll attach the script this Sunday.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.