Macro to separate, concatenate, and change sheet names

I am brand new to Macros - in fact, I really do not know what I am doing with them at all.

I would like to see if I can create a macro to even more simplify the repetitive tasks that I need to do. This will reference the Macro solution that was given to me in Post #4 on this link: http://www.excelforum.com/excel-gene...ml#post3679700

I gave a simplified version of what I am actually trying to do because I didn't expect as awesome of an answer as I got - and, it seems, with using a Macro, I could get Excel to do even more for me.

Here is a table of exactly what I am looking at


SHEET 1

A	B	C	D	E
Date&Time	Ask Price	Bid Price	Ask Volume	Bid Volume
2007.03.25 21:00:03.496	1.64845	1.64785	1	1

That is the basic setup and it goes for about 500,000 rows and I have almost 5,500 workbooks. So the idea of automating my tasks as much as possible is extremely appealing

Following the pattern on the link that I gave above, I would like the following:

ASK (This is Sheet #2 having been renamed)

A	B
20070325	210003;1.64845;1

(concatenation of Time, Ask Price, & Ask Volume...all separated only by a ";")

BID (Rename of Sheet 3)

A	B
20070325	210003;1.64785;1

(concatenation of Time, Bid Price, & Bid Volume...all separated only by a ";")

And, with the answer, could you also share with me precisely how I am supposed to enter this Macro into excel and also if there is some way of being able to this in a batch, rather than each file individually?

thank you very much and I deeply appreciate any help that anyone is able to give to me.

Hi, JGSmith,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

Hi, JGSmith,

I´ll take the easy part first about inserting VBA code

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

For the other question I would like to ask for a workbook with some samples instead of any pictures. The code could be placed in one workbook and all files could be opened, procesed and closed and saved without having to install the code there (this could be either a workbook or an AddIn).

Ciao,
Holger

Hi, JGSmith,

this is only for the file you provided to run a test and see if things head into what you finally want.

Please Login or Register  to view this content.

Ciao,
Holger

Hi, JGSmith,

for me it´s ALT+F8 to go to the macro box.

You should have only one macro of teh given name.

Have a look at the attachment, where I placed a button to start the macro.

Ciao,
Holger

Hi, JGSmith,

you don´t have the path

Please Login or Register  to view this content.

on your computer - not so surprising.

You would need to adapt the drive and folders as well as the name of the file to suit your requirements. You could use Application.GetOpenFilename or BrowseForFolder to get the information or place this information in cells on a worksheet but for the moment you should alter the information for the constants.

Ciao,
Holger

Hi, JGSmith,

if I remember correctly any textfile will only consist of one sheet, so could you please explain to me how to add sheets to these files? Check the folder and see if any processed file has been saved there, maybe open that and have a glimpse at what this file may look like.

Ciao,
Holger

Hi, JGSmith,

have a look at this:

Please Login or Register  to view this content.

Manipulation will take place on the same sheet, you could either leave empty columns between the areas or use a code to export the desired columns into textfiles.

Ciao,
Holger

Hi, JGSmith,

if you pasted the new procedure over the existing one you would need to adapt the correct name in the calling procedure.

Ciao,
Holger