Macro file name help?

gdclu1

New Member
Joined
Jan 23, 2005
Messages
3
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....
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

New Member
Joined
Jan 23, 2005
Messages
3
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

New Member
Joined
Jan 23, 2005
Messages
3
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
 

Forum statistics

Threads
1,148,524
Messages
5,747,183
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top