Run Macro for Multiples Open Files 1

[RooSXL]

I need to perform certain formatting to a series of open files. I came up with a small macro, that as I go along will expand into further formatting. My two biggest questions are:

1.- I am needing to specify in the macro manually before running it to variables to be changed in the worksheet. Is there a way to be prompted for this variables and once inputed in the first file, it does not ask in the next files to be ran?

2.- How do you loop/toggle between the files you are wanting to perform the formatting in the macro below without having to specifically name them in the macro?

Sub ChangeCellNameandSheetName()
    Sheets(1).Select
    Range("A3").Select
    Selection.NumberFormat = "@"
    ActiveCell.Formula = "2-18-05"
    Sheets(1).Name = "2-18"
End Sub

All inputs will greatly appreciated.

Oscar

 

[Shippwreck]

Hi,

If it is always a single cell then in the first file to run the macro you could select that cell on the sheet then run the macro with this code:

address1 = ActiveCell.Address

Now to loop through theme all. If the filenames are always the same then you could put these in an array:

filenames = array("fname1", "fname2", "fname3")

then to run through all you do:

For intI = 0 To 2
Workbooks.Open Filename:=filenames(intI)
Sheets(1).Select
Range(address1).Select
Selection.NumberFormat = "@"
ActiveCell.Formula = "2-18-05"
Sheets(1).Name = "2-18"
Next

see if that works, For the filenames you will need to put the entire file path, not just the name

Shippwreck

http://www.orlandoluxuryvilla-pool.com

 

[RooSXL]

Thank you very much. I am trying to change the content in cell A3. That is why I have to select it and then write in the macro this>> ActiveCell.Formula = "2-18-05"

After that I need the name to be changed, and that is why I need >> Sheets(1).Name = "2-18"

Is there a way to enter as "2-18-05" and "2-18" one time and be gone with it.

I will try right now with placing these two string into two cells and anchor them with the .address method and see if it works.

Thanks very much for your input on this matter.

Best

 

[Shippwreck]

Hi again,

I have been thinking about your problem and i think the following would work well, however it assumes all the files you need to change are in the same folder.

I would create a new excel file and put the following code in it:

Sub Test()

Application.ScreenUpdating = False

Range("A1").Select
Test1 = ActiveCell.Value
Range("A2").Select
Test2 = ActiveCell.Value
Range("A3").Select
Test3 = ActiveCell.Value

Range("A65536").Select
Selection.End(xlUp).Select
Last = ActiveCell.Row

PathName = ActiveWorkbook.Path
intY = 0
Dim FileNames(2) As String

For intX = 4 To Last
    Range("A" & intX).Select
    FileNames(intY) = ActiveCell.Value
    intY = intY + 1
Next

Last = Last - 4

For intI = 0 To Last
   Workbooks.Open Filename:=PathName & "\" & FileNames(intI)
   Sheets(1).Select
   Range(Test1).Select
   Selection.NumberFormat = "@"
   ActiveCell.Formula = Test2
   Sheets(1).Name = Test3
Next

msgbox ("complete")

End Sub

You then need to place in the worksheet the values.

In Cell A1, place the cell address you want to change, ie A3
In A2 you place the value that you want the cell to change to
In A3 you place the value you want the sheet to change to
and in A4 to A... you place the names of the files you need changing (and only the names not the path, should work without the.xls too)

The only real limitation is that you need to change the number in the following section:

Dim FileNames(2) As String

to the number of files you have.

Basically what the code does is:
firstly stop the screen from updating till its finished, (looks nicer that way and runs faster.) It then assigns the value in cells A1 to A3 to variables for use later. It then finds the row number of the last filename and stores the number in Last. Then it finds the path of the current workbook (hence the need for it to be saved in the same place as the filed that need changing) and sets up the array.

Now the fun bit. The first For loop populates the array with the filenames specified. The second for loop then works through the array opening each file and changing the values. If you don't wish all the files to stay open then add a close file statement in here (can't remember it off the top of my head) Lastly it displays a box to let you know its finished.

Now the code should work just cut and paste it however it is not the "cleanest" or most well written as i am fairly new to VBA myself so if anyone has tips on cleaning it up then please let me know (and i know i should always define my variables but i'm lazy)

anyway hope it works

Matt

http://www.orlandoluxuryvilla-pool.com