How to make changes in the target file if the codes are in a different file

user_16826211 0
Viewed 37

My code is written in my macro workbook. I have to work on a different file every time with the same steps. So ideally I need a macro in a macro file and my target files will be different. Below is a code I have in my macro file.

    Sub EEBalanceSummary()
    On Error Resume Next
    Dim FilePath As String
    FilePath = Application.GetOpenFilename
    Range("B6").Select    
    End Sub

I run this code from my macro file. It asks me to select the folder and then the file I need to work on. Now, instead of selecting "B6" cell in my newly opened target file, it selects "B6" in the same macro file. How can I get the code to work on my target file?

vba excel
1 Answers

Your code Range("B6").Select is short for Application.ActiveWorkbook.ActiveSheet.Range("B6").Select.

You want to instead specify the specific open workbook and worsksheet, for example like this:

Workbooks("Example.xlsx").Sheets("Tabname").Range("B6").Select

You could also research:

  • How, when and why to avoid using .Select.
  • Using variables or constants for workbook, worksheet and range (to avoid inputting their names or addresses inline in the commands within your code).
  • Difference between ThisWorkbook, ActiveWorkbook and Workbooks()
user_141529890
Related