Cannot change active Workbook

[Flicules]

Hi guys,

I am new to the world of VBA and i have just attempted to do a little coding.

My task is pretty simple:
- 2 Workbooks with multiple sheets each;
- find a Question number in column A in the first Workbook and it`s corresponding answer(YES, No or N/A) and then transfer it to the next workbook.

The problem i have is that i cannot switch from one active workbook to the other. I have searched all over the forums on how change between active workbooks and found that workbook("Wbname").Activate command as the one to use.

Unfortunately, this does not work for me(code in red). My code will search in the current workbook and not into account the one indicated.

Both workbooks are opened. I don`t any ideas on how to fix this.
Please, if you have any suggestions, i would really appreciate it

Thanks!


The code is the following:


Sub Sumary()

Dim i As Integer 'Variable used to store the line number of current question
Dim j As Range 'Variable used to iterate through the elements of the A collumn
Dim l As Range 'Variable used to iterate through the elements of the C collumn(Sumary workbook)
Dim temp_Question_nr As Integer 'Variable used to store the question number
Dim temp_Answer As Integer 'Variable used to store the answer to the current question


For Each j In Range("A1:A25")

If IsNumeric(j) And Not IsEmpty(j) Then
temp_Question_nr = j.Value
Cells(100, 1) = temp_Question_nr

If j.Offset(0, 4).Value = "X" Then
temp_Answer = 1

ElseIf j.Offset(0, 5).Value = "X" Then
temp_Answer = 2

ElseIf j.Offset(0, 6).Value = "X" Then
temp_Answer = 0

End If

'---------------------------------------------------------------------------------------------------------------------------


Workbooks("Summary Q1 FY12 test.xlsm").Activate
Worksheets("AUSTRALIA").Select


For Each l In Range("C1:C25")

If l.Value = temp_Question_nr Then

If temp_Answer = 1 Then
l.Offset(2, 0).Value = "X"
ElseIf temp_Answer = 2 Then
l.Offset(3, 0).Value = "X"
ElseIf temp_Answer = 0 Then
l.Offset(4, 0).Value = "X"
End If
End If
Next l

Workbooks("Report Australia Q1 test.xlsm").Activate
Worksheets("Company-Level Controls").Select
Worksheets("Company-Level Controls").Activate

End If

Next j

End Sub

 

[SQUIDD]

Hi

ChDir "your directory"
Workbooks.Open Filename:="filename"

this is how i have opened different workbooks in the past,

may help

dave

 

[Flicules]

Hi SQUIDD,

Thank you for your fast reply.

I have tried your suggestion but, unfortunately, the behavior of the code is the same.
When it reaches to the second for loop, the search will be done in the current workbook.

Is the workbook change not allowed within a for loop?

I really don`t know why it behaves like this.

 

[Flicules]

Hi guys,

I have found the solution to my problem...or maybe is more appropriate to say, i have found my error.

My code was assigned to one Sheet(Microsoft Excel Object) within the first Workbook and now, i have executed the code in the Modules section.

It works ok. Now just need to improve it and make it a little better

Thank you for your help!