Mastersheet and Dashboardsheet - Retrive, add new, and save data to mastersheet.

[Golaidron]

Hi guys.
Feeling my head is exploding because I can not find a solution for what I want.
I have a mastersheet with data with approx. 1000 customers in column A and approx. 50 columns of data.
I want to have another excel sheet where I can:

1. Search for a customer
2. If not found, add new customer
3. If found, retrieve all the data for the customer in different columns and row
4. The opportunity to edit this information
5. The opportunity to save new informastion to the mastersheet, so that it will show correct informastion next time I search for the customer

Do any of you have any tips on how to do this? Preferably without using a userform

 

[RasGhul]

There is a built in Userform without the need for VBA which you could do all the above?

Userform no VBA

 

[vcoolio]

Hello Golaidron,

Another option is to automate the AutoFilter within a worksheet_change event code such as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

        Dim wsM As Worksheet: Set wsM = Sheets("Master")
        Dim wsS As Worksheet: Set wsS = Sheets("Search")

Application.ScreenUpdating = False

        wsM.[A1].CurrentRegion.Sort wsM.[A2]
        
        With wsM.Range("A1", wsM.Range("A" & wsM.Rows.Count).End(xlUp))
                .AutoFilter 1, Target.Value
                .Offset(1).EntireRow.Copy wsS.Range("A" & Rows.Count).End(3)(2)
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

wsS.[A1].ClearContents
Application.ScreenUpdating = True

End Sub

This code needs to be placed into the Master sheet module.

Hence, if you had all your customer names in cell A1 (using a data validation drop down), in a search sheet, all the data for the selected customer would be filtered in the Master sheet, transferred to the search sheet then deleted from the Master sheet. Once in the search sheet, you can update the customer data as required then click on a button to send the updated data back to the Master sheet with the search sheet then being cleared ready for the next lot of data. Even if there aren't any updates required, still click on the button to send the customer data back to the Master sheet. The following code assigned to a button could do this for you:-

Option Explicit

Sub Update()

Application.ScreenUpdating = False

        Dim wsM As Worksheet: Set wsM = Sheets("Master")
        Dim wsS As Worksheet: Set wsS = Sheets("Search")
        
        wsS.[A3].CurrentRegion.Offset(1).Copy wsM.Range("A" & Rows.Count).End(3)(2)
        wsS.[A3].CurrentRegion.Offset(1).Delete
        wsS.[A1].ClearContents
        wsM.[A1].CurrentRegion.Sort wsM.[A2]
        
Application.ScreenUpdating = True

End Sub

This code needs to be placed into a standard module.

I've attached a mock-up file here. You can test with this to see if it is what you're hoping for. Just select a customer from the drop down in A1 of the search sheet. There is an "UPDATE" button on the Search sheet. Play around with the transferred data then click on the button. Please note that the file will only be available on the file sharing site for one week.

To speed things up, the codes sort the data first.

I hope that this helps.

Cheerio,
vcoolio.