Copying information from 4 different Excel sheets

[NeVeR]

Hi all.

 

I'm trying to make a Macro that copies information from 4 different Excel sheets and then pastes it into a notepad file.

 

I've it working on 1 spread sheet. But it's getting the other 3 to work as well is the problem.

 

This infomation/spread sheet will change in name and info each day. I hope you know that i mean. This is a daily task under taken by a member of staff each day and each spread sheet is save under a new name each day (4 each day)

 

Is something like this possible where it will open 4 spread sheets ?. Or would something like a key press on each spread sheet to activate work better ?.. Like you get a prompt saying "Open SS 1 and press Enter" once copied you get another saying " Open SS2 and press Enter" and so on.. Once the 4th is done you get your Notepad open with the info.

 

 

Sorry if this sounds all over the place.

 

 

Also how to I get Macro Express to start a new mail in Microsoft Express... The comand in Microsoft Express is CRTL+N but anytime i try it Macro express it opens a note .. which is the short cut CRTL+SHIFT+N but i'm not pressing that.

 

Any help would be great. thanks.

[kevin]

Not sure about the Excel questions. Perhaps others can help.

 

Also how to I get Macro Express to start a new mail in Microsoft Express... The comand in Microsoft Express is CRTL+N but anytime i try it Macro express it opens a note .. which is the short cut CRTL+SHIFT+N but i'm not pressing that.

When you use Text Type Ctrl+N you are sending Ctrl+Shift+N. This is what is sent:

<Ctrl Down>
<Shift Down>
n
<Shift Up>
<Ctrl Up>

Change your Text Type to Ctrl+n. (Use lower case n!)

[NeVeR]

Not sure about the Excel questions. Perhaps others can help.

 

 

When you use Text Type Ctrl+N you are sending Ctrl+Shift+N. This is what is sent:

<Ctrl Down>
<Shift Down>
n
<Shift Up>
<Ctrl Up>

Change your Text Type to Ctrl+n. (Use lower case n!)

 

 

Thanks this did work.

 

Cheers

[acantor]

Not sure that this will help, but there are built in keyboard shortcuts in Excel that might at least allow a brute force solution.

 

For example, to switch between worksheets, use Ctrl + PageUp and Ctrl + PageDown. If the four spreadsheets are kept as four worksheets in the same file, you could script something like this that would always go to the leftmost worksheet:

 

Type Text <CONTROL><PAGEUP>

Type Text <CONTROL><PAGEUP>

Type Text <CONTROL><PAGEUP>

Type Text <CONTROL><PAGEUP>

 

And then, to switch to the second:

 

Type Text <CONTROL><PAGEDOWN>

 

You can switch between different spreadsheets with Ctrl + F6 and Shift + Ctrl+ F6.

[NeVeR]

Not sure that this will help, but there are built in keyboard shortcuts in Excel that might at least allow a brute force solution.

 

For example, to switch between worksheets, use Ctrl + PageUp and Ctrl + PageDown. If the four spreadsheets are kept as four worksheets in the same file, you could script something like this that would always go to the leftmost worksheet:

 

Type Text <CONTROL><PAGEUP>

Type Text <CONTROL><PAGEUP>

Type Text <CONTROL><PAGEUP>

Type Text <CONTROL><PAGEUP>

 

And then, to switch to the second:

 

Type Text <CONTROL><PAGEDOWN>

 

You can switch between different spreadsheets with Ctrl + F6 and Shift + Ctrl+ F6.

 

Thank you for your reply.

This wont work as each spread sheet is a different file. All 4 are not on different pages.

 

What i've done so far is put in a stop command and continue on Key press. Also a text pop up informing the user to open the 2nd sheet and press "shift" to re-start the macro.. and then again for the 3rd and 4th. which is working at the moment. But been able to have it do it all on it's own is the goal.

 

Thanks for the reply.

[acantor]

This wont work as each spread sheet is a different file. All 4 are not on different pages.

 

Then experiment with Ctrl + F6. Although you say that the names of the four files can change, are there any consistencies? For example, is there a unique word in each of the file names? And if not, could you establish a file naming convention?

 

Assuming there is a unique word (or phrase) in each file name, this should be possible:

 

1. Manually open the four spreadsheets.

2. Activate the macro.

 

The macro visits each file in turn, extracts the file name from the window title, and saves them as text variables. Then check each variable for the unique word(s) that identify the spreadsheet:

 

If %T1% contains "expenses"

Activate Window %T1%

// Slight delay

// Do what you want to do with this file

// Maybe close it when finished?

etc.

End if