Searching for all open workbooks +

Hi everybody

I have a problem with a piece of code that I would like to optimize a bit.

What I currently have is;

Code:

---

Sub ovfData()
Dim j As Double
Dim k As Double
Dim iSheet As Worksheet
Dim nSheet As Worksheet
Dim oSheet As Worksheet
Dim ksheet As Worksheet
Dim bsheet As Worksheet
Dim dsheet As Worksheet
Dim dtSheet As Worksheet
     
    'Names the different sheets to be copied
    Set dsheet = ThisWorkbook.Sheets("Data")
    Set dtSheet = Workbooks("A1.xls").Worksheets("Febos")
       
Application.ScreenUpdating = False
 
'Clears the sheet Data for new data to be transferred
dsheet.Activate
dsheet.Range(Cells(1, 1), Cells(65536, 256)).Clear
dtSheet.Activate
dtSheet.Range(Cells(1, 1), Cells(1, 1).End(xlToRight).End(xlDown)).Copy dsheet.Cells(1, 1)

dsheet.Activate

'Deletes empty columns
For j = 256 To 1 Step -1
    If dsheet.Cells(1, j).Value = "" Then
        dsheet.Columns(j).Delete
    End If
Next j

Application.ScreenUpdating = True

End Sub

---

And what I would like is to avoid the part where I name the sheet form the 'other' workbook. I know upfront that my different workbooks are called (A1, A2, A3....A6, B1, B2) - but sometimes I don't have all of them open (The sheets in the workbooks are always named the same).

So I would like to find a way to loop through the names, if the name of the workbook is open then copy the data - and then the rest of my program does some processing and I get an output.

Any ideas?

/Nicolaj

to get the open workbooks, try like

vb Code:

Dim w As Workbook
For Each w In Workbooks
    Debug.Print w.Name
Next

Of course

Thank you very much!