Forum Discussion

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

excel

NikolinoDE

Platinum 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

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
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
- juan_landazuri
  Copper Contributor
  Sep 01, 2024
  Laura1980
  Where you able to make it work? How?
  Thank you
NikolinoDE
Platinum Contributor
Nov 12, 2020
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)