Activate next workbook

[Mladen82]

Hi all,

Every day I export 4 workbooks from Navision. I would like to create a macro that will put all 4 workbooks into one and then do some calculations. The problem is that these workbooks have different names every day. Is there a way to activate the next workbook regardless of the name?

 

[jmacleary]

Hello there. You can reference workbooks by number or in a for each loop - here are both ways:

Sub ListBooks()
Dim wkbk As Workbook
For Each wkbk In Workbooks
   MsgBox wkbk.Name
Next
End Sub
Sub AnotherWay()
For i = 1 To Workbooks.Count
MsgBox Workbooks(i).Name
Next i
End Sub

 

[Mladen82]

I think you dont understand me. I want to copy the data from all the workbooks to the first workbook so that I can work with the data later. However, in order to use "Workbooks (" ???. XLS "). Activate" I first need to rename them because each day is a different name. So I need to switch to another workbook to change my name. Later when I rename all 4 Workbooks to 1.xls 2.xls 3.xls and 4.xls then I can call them when I need them.

 

[jmacleary]

Hi there. Yes, I understand your goal - the code I presented is one way of accessing all the workbooks without needing to know their names. This code should help you a bit more:

Sub DoBooks()
Dim wkbk As Workbook
For Each wkbk In Workbooks
If wkbk.Name <> "MASTER" Then  'change MASTER to whatever you want your concatenated workbook to be called
   wkbk.Activate
 ' in here you can do the copy/paste etc. on each workbook in turn - don't use the name, just use wkbk
' if you are going to do any copy paste etc. make sure you switch between your MASTER wokbook and wkbk as necessary
End If

Next
End Sub