Public activeCellAddress As String 'Private Sub WorksheetActivate() Private Sub…
- User
Public activeCellAddress As String 'Private Sub Worksheet_Activate() Private Sub Worksheet_Deactivate() activeCellAddress = activeCell.Address End Sub
Function getCompanyName() As String Dim providersSheet As Worksheet Set providersSheet = ThisWorkbook.Sheets("Providers") Dim activeRow As Range Set activeRow = providersSheet.Range(activeCellAddress).EntireRow getCompanyName = activeRow.Cells(1, 3).Value End Function
Private Sub Worksheet_Open(ByVal Target As Range) Application.CalculateFullRebuild Call populateCompanyData End Sub
Sub populateCompanyData() Dim companySheet As Worksheet Set companySheet = ActiveWorkbook.ActiveSheet Dim lastRow As Long lastRow = companySheet.Cells(Rows.Count, 1).End(xlUp).Row Dim emptyRow As Long emptyRow = lastRow + 1 Dim activeCellAddress As String 'define variable here activeCellAddress = Application.Caller.Address 'retrieve address of calling cell companySheet.Cells(emptyRow, 1).Value = getCompanyName() companySheet.Cells(emptyRow, 4).Value = ThisWorkbook.Sheets("Providers").Range(activeCellAddress).Offset(0, 1).Value End Sub
- Assistant
As an AI language model, I cannot provide you with an opinion, but I can tell you that this code is used to populate data from a "Providers" sheet to another sheet called "companySheet" based on the active cell in the providers sheet. It retrieves the active cell's address, gets the company name from the active row on the providers sheet, and then populates the data on the company sheet.
- User
What I want to know is where this code Private Sub Worksheet_Deactivate() should be located, where the Function getCompanyName() As String should be located and where the Sub populateCompanyData() should be located. They are all currently in a sheet called ADH (not companySheet) and the code is not working
- Assistant
The code
Private Sub Worksheet_Deactivate()should be located within the sheet module where you want to track which cell was last active before moving to another sheet.The Function
getCompanyName() As Stringshould also be located within the same sheet module where you want to retrieve the value of the third column (company name) of the active row on the "Providers" sheet.The Sub
populateCompanyData()should be located within the same module as well, where you want to populate data onto the "companySheet" based on the active cell in the "Providers" sheet.Make sure that the code is correctly entered into the sheet module for the "ADH" sheet and that the sheet names are correctly specified. Also, make sure that the "ADH" sheet is the active sheet when you run the code.