Working with excel

[RKrishna77]

I need to do same work at same cell spaces in different excel files, is there a way to do that once.

Any help is appreciated

Thanks.
RK

 

[rdodge]

Yes, you can do that via VBA. Even in VBA, you will only be able to have it do one file at a time, but it will save you time. Have a macro setup in a workbook that will remain open during the whole time. The macro would be setup something like the following:

Sub WorksheetList()
Dim I as Long, FR as Long, LR as Long, WS as Worksheet
Dim WB as Workbook
Set WS = Thisworkbook.Worksheets("Sheet1&quot
If IsBlank(WS.Range("A4&quot) Then
Msgbox "There is no files listed to have a cell task to be done.",48
Exit Sub
End If
FR = 4
If IsBlank(WS.Range("A5&quot) Then
LR = 4
Else
LR = WS.Range("A4&quot.End(xlDown).Row
End If
For I = FR to LR Step 1
Set WB = Workbooks.Open WS.Range("A" & I).Value
PerformCellTasks WB.Worksheets(WS.Range("B" & I).Value)
WB.Close
Next I
End Sub

Sub PerformCellTasks(ByRef WS as Worksheet)
With WS.Range("B4&quot
.Font.Bold=True
.Interior.ColorIndex = 5
End With
End Sub

In the same workbook that you put the code in, starting with row 4, put in the fill file pathname and spec name in the A column and the worksheet name in column B.

Example:

Cell A4

C:\MyDocuments\Book1.xls

Cell B4

Sheet1

and keep filling in until you have all of the workbooks listed in the file. You will need to adjust the above code for the worksheet name of the workbook that you put this code in.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[RKrishna77]

Thanks a lot Roanld you made my work easy.

Ravi.

 

[rdodge]

Just realized, I didn't include the parantheses on the line below:

Set WB = Workbooks.Open WS.Range("A" & I).Value

So change this line to:

Set WB = Workbooks.Open(WS.Range("A" & I).Value)

Main reason for the addition of the parantheses, it's being used like a function call, as opposed to when it's used as a subprocedure, you don't include the parantheses

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.