0

I wrote a simple VBA code to automate writing of Vlookup formulas. After testing the code I noticed that it is not working correctly when I select a Range from a different workbook. It creates Vlookup formula as intented but it does not switch to initial workbook. It stays focused on a Workbook which I used to select a range. As much as i noticed while debuging it references the correct workbook and sheet but it does not change focus for some reason.

If anyone has any ideas I would appreciate it. Thank you.

Sub vlookup_easy()


Dim Rng As Range
Dim shOriginal As String
Dim wbOriginal As String


Dim frmWS As String
Dim frmWb As String
Dim sRange As String
Dim iColumn As Integer

shOriginal = ActiveSheet.Name
wbOriginal = ActiveWorkbook.Name

Set Rng = Application.InputBox(Prompt:="Unestie Range za Vlookup formulu", Title:="Vlookup", Default:=Selection.Address, Type:=8)

sRange = Rng.Address
frmWS = Rng.Parent.Name
frmWb = Rng.Parent.Parent.Name

iColumn = Application.InputBox(Prompt:="Unestie indeks kolone za Vlookup formulu", Title:="Vlookup", Default:=2, Type:=1)

ActiveCell.Formula = "=VLOOKUP(" & ActiveCell.Offset(0, -1).Address(False, False) & ",'[" & frmWb & "]" & frmWS & "'!" & sRange & "," & iColumn & ",FALSE)"

Workbooks(wbOriginal).Sheets(shOriginal).Activate  'this part is not working correctly


End Sub
milantop
  • 1
  • You appear to be activating the active sheet? – SJR May 27 '22 at 09:41
  • I am referencing the sheet that is active at the moment when I initiate the code. However, it changes the focus when I choose a range (line "Set Rng =...) and it does not switch it back for some reason. This only happens when I choose a range from a different workbook, if a choose a range in the same workbook it works perfectly. – milantop May 27 '22 at 10:11
  • So you are changing focus to a different file. You would at least have to activate the workbook and then activate the sheet. But note that you don't need to activate anything in code generally. – SJR May 27 '22 at 10:17
  • I tried referencing just a workbook but it still does not work. If you have a suggestion how to change focus without activating workbook please write it. Any suggestion how to improve code is welcome. – milantop May 27 '22 at 10:32
  • https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Your code must be erroring. – SJR May 27 '22 at 10:34

0 Answers0