Macro file name help?

[gdclu1]

Hi, a workmate pointed me in the direction of this site so i hope someone on here will be kind enough to help as i don't use excel all that often!!

I have many spreadsheet files from which i need to copy and paste just some data from each onto one new big spreadsheet. because the data i need is in the same place on each file i thought i'd just record my actions of copying and pasting the first lot of data as a macro then simply use this macro for every file thereafter....you can probably already guess my question....

the macro only works for the file i first used unless i go in and change the file name in every line manually, i'm pretty sure there's an easier way. how can i set up a macro to not care what the file name is and just work between the two open spreadsheet windows?

many thanks in advance....

 

[tactps]

You will find that in hte macro, it will refer to something like:

Windows("filename") or Sheets("filename").

Replace the "filename" with the sheet number you want to use, e.g.:

Windows(2).select

 

[gdclu1]

hmmmm, thanks but it didn't seem to work, though it may be that i'm applying it incorrectly. here is the current code for the macro -

Selection.Copy
Windows("statistic excell sheet.xls").Activate
ActiveSheet.Paste
Windows("Day 7 - AMBL Distance.xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("statistic excell sheet.xls").Activate
ActiveCell.Offset(8, 0).Range("A1").Select
ActiveSheet.Paste
Windows("Day 7 - AMBL Distance.xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("statistic excell sheet.xls").Activate
ActiveCell.Offset(8, 0).Range("A1").Select
ActiveSheet.Paste
Windows("Day 7 - AMBL Distance.xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("statistic excell sheet.xls").Activate
ActiveCell.Offset(8, 0).Range("A1").Select
ActiveSheet.Paste

and so on.....

so yeah i'm copying into "statistic excell sheet" and basically i just want to be able to use this on any file not just "day 7 - ambl distance"?

cheers

 

[tactps]

Firstly, change the "activate" to "select". Then replace:
"Day 7 - AMBL Distance.xls" with the window number (may take trial and error to get the right sheet).

 

[gdclu1]

Cheers for your help - i changed the filename to (2) but just needed to leave activate rather than change it to select.
thanks again, from a fellow melbournian