[Solved] Linking 2 Spreadsheets and pulling out specific data

[dsantone]

I am working in 2 spreadsheets, the one spreadsheet has multiple columns and rows, and it is updated daily. When it is updated though a lot of times the person entering the data will insert row and enter the information, I am looking to have my 2nd spreadsheet automatically updated when new information in put into the 1st spreadsheet. I then want to take the information that was automatically updated and filter into and update one of the other tabs within the same workbook. Thank you

 

[Yousuf Shovon]

I am working in 2 spreadsheets, the one spreadsheet has multiple columns and rows, and it is updated daily. When it is updated though a lot of times the person entering the data will insert row and enter the information, I am looking to have my 2nd spreadsheet automatically updated when new information in put into the 1st spreadsheet. I then want to take the information that was automatically updated and filter into and update one of the other tabs within the same workbook. Thank you

Dear DSANTONE,

Welcome to our ExcelDemy Forum! Thanks for the detailed explanation. Though it would be more helpful if you could share an example file. Thanks again.

Regards,
Yousuf Shovon

 

[Yousuf Shovon]

I am working in 2 spreadsheets, the one spreadsheet has multiple columns and rows, and it is updated daily. When it is updated though a lot of times the person entering the data will insert row and enter the information, I am looking to have my 2nd spreadsheet automatically updated when new information in put into the 1st spreadsheet. I then want to take the information that was automatically updated and filter into and update one of the other tabs within the same workbook. Thank you

Dear DSANTONE,

I understand you wish to link two sheets with auto-update properties and also open a new sheet that will contain the updated values. Fortunately, you can do this using table reference properties and a VBA code. Here are the steps to do so:

• Firstly, select the whole range of the source data and go to Insert > Table.
• Afterward, name the table as Group as shown below.​

​

• Now, go to the sheet to which you wish to link the data.​
• Type =Group in A1 to link the sheets.​
• As a result, you can write anything in the source sheet to get the update in the other sheet.​

Note: Go to https://www.exceldemy.com/automatically-update-one-excel-sheet-from-another-sheet/ article to get other methods to link the sheets.

​

• Next, to get the updated data in a new sheet, save the below code in a module box.​

Sub Click_Update()
    Dim NewSheet, ws As Worksheet
    Dim SourceSheet As Worksheet
    Dim DataRange As Range
    Dim LastRow As Long
    Dim NewLastRow As Long
    Dim NewData As Variant
   
    Set SourceSheet = Worksheets("Source")
  
    Set DataRange = SourceSheet.Range("A1:Z100")
      
        LastRow = SourceSheet.Cells(SourceSheet.Rows.Count, "A").End(xlUp).Row
        NewData = DataRange.Resize(1, LastRow).Offset(LastRow - 1, 0).Value
       
        Set NewSheet = ThisWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count))
       
        For Each ws In ActiveWorkbook.Worksheets

        If ws.Name = "New Data Sheet" Then
           
            Application.DisplayAlerts = False
            ThisWorkbook.Sheets("New Data Sheet").Delete
            Application.DisplayAlerts = True

            Exit For

        End If

    Next ws
   
        With NewSheet
             
            .Name = "New Data Sheet"
            .Range("A1:C1").Value = NewData
        End With
   
End Sub

• Later, assign a macro in a button. To do so, go to Developer > Insert > Button.​

​

• There, name the button Update and right-click on it. Then, go to Assign Macro.​

​

• Lastly, tap on Click_Update and OK.

​

• Hence, clicking on the Update button after each entry will update the linked sheet and also open a new sheet with the updated data.

I have attached the Excel file for a better understanding. Please try it and let me know if it works. Thank you.

Regards,
Yousuf Shovon