Forum Discussion

Laura1980's avatar
Laura1980
Copper Contributor
Nov 11, 2020

Copy data to another Workbook automatically

Hi All,

 

Please could I get some help automatically copying a data I put into a set section of a workbook into another work book?  Is this possible?

 

We have a large spreadsheet with a lot of information in it.  Ultimately I would only like to share a set section of each worksheet within this workbook.  Can I hide the other cells on each worksheet from non-authorised users, or can I get the data in this set area of each worksheet to auto copy to another separate workbook?

 

Any help would be appreciated as at the moment I am having to duplicate the data into a separate workbook and this is ultimately going to lead to mistakes and it's taking up a lot of my time each day.

 

Thank you 🙂 

Excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 11, 2020

    Laura1980 

     
    Here is a rough approach that needs to be adapted to your needs.
    Copy to a module and run.
     

     

    'Has been fished from the Internet ... 
'it's a shame that I don't know the site anymore, there might be several examples.

Option Explicit
 
Sub data ()
Dim blnData As Boolean
Dim i &, n, lngRow &
Dim fileA $
Dim wksFileB As Worksheet
Dim wksFileA As Worksheet
 
'Please adjust the table name of file B here
Set wksDateiB = ThisWorkbook.Sheets ("Table1")
 
With application
    .ScreenUpdating = False
    .EnableEvents = False
End With
 
'Please adjust the storage path \ file name here!
'By entering the character "*" at the end of the file name you are flexible with the file name assignment
'BSP: FileA Rev1.1 or FileA Rev1.2 etc ...
FileA $ = Dir ("C: \ Users \ Nikolino \ Desktop \ FileA *")
'Please adjust the storage path here!
Workbooks.Open "C: \ Users \ Nikolino \ Desktop \" & FileA $
'Please adjust the table name of file A here
Set wksDateiA = Workbooks (FileA $). Sheets ("Table1")
 
With wksFileB
    lngRow & = .Cells (.Rows.Count, 1) .End (xlUp) .Row
    For i = 1 To lngRow &
        n = IIf (.Cells (i, 1) <> "", Application.Match (.Cells (i, 1), _
            wksFileA.Columns (1), 0), False)
        If IsNumeric (n) Then
            .Cells (i, 2) .Value = wksDateiA.Cells (n, 2) .Value
            blnData = True
        End If
    Next i
    Workbooks (FileA $). Close False
End With
 
Set wksDateiA = Nothing
Set wksDateiB = Nothing
 
With application
    .ScreenUpdating = True
    .EnableEvents = True
End With
 
If blnData Then
    MsgBox "Data has been updated.", VbInformation, "INFO: NEW DATA"
    Else
    MsgBox "No data was found!", VbInformation, "INFO: NO DATA FOUND"
End If
 
End Sub

     

     

    Hope I could help you at least a little.

    Thank you for your patience and time.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    • Duane Bergh's avatar
      Duane Bergh
      Copper Contributor
      Nov 11, 2020

      NikolinoDEIf you have the file saved in a SharePoint library, you should be able to use Power Automate to move rows from one spreadsheet to another based on a status column. You could set the workflow to run on a schedule that looks for all rows in the table set to a "Hide" status, create them in a new workbook stored in the same document library and finally loop back round to the original to delete. 

      • Laura1980's avatar
        Laura1980
        Copper Contributor
        Nov 12, 2020

        Duane Bergh  Hi Duane,

        Thank you for getting back to me. I think I understand what you are suggesting but I do not need it to delete anything. I just want the information I put in columns K to AB to be copied exactly into a separate workbook for my staff to view.  The original document holds sensitive data in columns A to J that I don't really want them seeing. 

         

        As I then update the data in columns K to AB in the original I would like the data to automatically update in the secondary workbook that my staff have access to.  

         

        Sorry if I am not being very detailed, I have never tried to do anything this advanced before.

         

        Thank you,

         

        Laura

Resources