Copy data to another Workbook automatically

Copper Contributor

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 :) 

4 Replies

@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)

 

@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. 

@Duane Bergh 

It is always advantageous if the Excel version and the operating system were known from the outset, everyone would surely save a lot of time, me too.
I can understand why many do not want to answer questions that do not have precise details.

But I think getting a wrong answer is always better than not getting an answer.

In this sense, please ignore my suggested solution.

 

Sorry that I took your time and couldn't give you a solution.

 

Wish you a nice day.

 

Nikolino

I know I don't know anything (Socrates)

@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