Activate Window

[marcjwright]

I'm new to macros in excel so I must apologise first if what i'm asking seems silly.

I'm trying to copy from one master file formats and formulas only to a variety of existing files. When recording the macro it works fine but the macro specifically states to activate the original file upon which i have recorded the macro after copying from the master. How can i change this so it uses the active file when i run the macro. I have included the macro below and have highlighted the bit i want to change so its not specific. Is there any way i coukld just specify a directory where the file would be and the it would run on all those files?


Sub formatting()
'
' formatting Macro
' Macro recorded 08/01/2007 by Marc Jonathan Wright
'
' Keyboard Shortcut: Ctrl+q
'
Windows("New Large Export File Master.xls").Activate
Cells.Select
Range("BC23").Activate
Selection.Copy
Windows("060001.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
Range("BC2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(Calculations!R[19]C[-53]="""",0,'Special Price Offer'!R4C9)"
Range("BC2").Select
Selection.Copy
Range("BC2:BC76").Select
ActiveSheet.Paste
Range("BC19:BD20").Select




End Sub

[grahammal]

I think I understand your problem.
All you need to do is generate a new Macro button in your new spreadsheet and copy the same code into it from the original spreadsheet.
Its not the code that causes this reverting back to the original spreadsheet but the macro button itself.

[mudraker]

What method are you using to start your macro

The code no longer activates New Large Export File Master.xls
It now copies the cells from the active workbook and pastes into 060001.xls

I have also removed some surplus code

Please Login or Register  to view this content.

Coding can be added to work on all files within a folder.

Let us know how the above changes workout then we will look into the looping through the files problem

[marcjwright]

Thanks, but what i'm trying to do rather than paste from the active workbook into 060001 is that i'm trying to copy into the active workbook from the master. The problerm is that all these files are existing and i just want to change the formats but there are about 1000 of them! My problem is that whenever i run the macro (if looking at my original code) it will activate the master and copy from that, not a problem, but then it tries to activate the 060001 file but i'll only have the master and 060002 open so it won't run.

If there is a way to do it from all files in a folder that would be excellent because then i only need to do it 12 times!

And i'm running the macro from the play macro button on the macro toolbar in excel

[mudraker]

Lets try this version.


If your master workbook has mare than one sheet you may need to replace the sheets(1) in this line of code with the sheet name. If you do then make sure the name in enclosed by " the same as the workbook name is.

Workbooks("New Large Export File Master.xls").Sheets(1).Cells.Copy

Please Login or Register  to view this content.

[marcjwright]

Thanks, worked perfectly

[mudraker]

Now that we have the 1st part working lets see if we canm get the 2nd part of your problem resolved - Opening every file in a folder

This version asks you to select a file in the directory you want to process.
It will then open a file, format the file, close & save file.
Repeat above step until all files in the directory have been processed (You are only asked to select a file once

Try on a backup copy of your files

Please Login or Register  to view this content.