Copy data to another Workbook automatically

%3CLINGO-SUB%20id%3D%22lingo-sub-1877229%22%20slang%3D%22en-US%22%3ECopy%20data%20to%20another%20Workbook%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877229%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20could%20I%20get%20some%20help%20automatically%20copying%20a%20data%20I%20put%20into%20a%20set%20section%20of%20a%20workbook%20into%20another%20work%20book%3F%26nbsp%3B%20Is%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20a%20large%20spreadsheet%20with%20a%20lot%20of%20information%20in%20it.%26nbsp%3B%20Ultimately%20I%20would%20only%20like%20to%20share%20a%20set%20section%20of%20each%20worksheet%20within%20this%20workbook.%26nbsp%3B%20Can%20I%20hide%20the%20other%20cells%20on%20each%20worksheet%20from%20non-authorised%20users%2C%20or%20can%20I%20get%20the%20data%20in%20this%20set%20area%20of%20each%20worksheet%20to%20auto%20copy%20to%20another%20separate%20workbook%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated%20as%20at%20the%20moment%20I%20am%20having%20to%20duplicate%20the%20data%20into%20a%20separate%20workbook%20and%20this%20is%20ultimately%20going%20to%20lead%20to%20mistakes%20and%20it's%20taking%20up%20a%20lot%20of%20my%20time%20each%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1877229%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1877598%22%20slang%3D%22de-DE%22%3ESubject%3A%20Copy%20data%20to%20another%20workbook%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877598%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F865381%22%20target%3D%22_blank%22%3E%40Laura1980%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22QFw9Te%20BLojaf%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22dePhmb%22%3E%3CDIV%20class%3D%22eyKpYb%22%3E%3CDIV%20class%3D%22J0lOec%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EHere%20is%20a%20rough%20approach%20that%20needs%20to%20be%20adapted%20to%20your%20needs.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22J0lOec%22%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3ECopy%20to%20a%20module%20and%20run.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22J0lOec%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E'Has%20been%20fished%20from%20the%20Internet%20...%20%0A'it's%20a%20shame%20that%20I%20don't%20know%20the%20site%20anymore%2C%20there%20might%20be%20several%20examples.%0A%0AOption%20Explicit%0A%20%0ASub%20data%20()%0ADim%20blnData%20As%20Boolean%0ADim%20i%20%26amp%3B%2C%20n%2C%20lngRow%20%26amp%3B%0ADim%20fileA%20%24%0ADim%20wksFileB%20As%20Worksheet%0ADim%20wksFileA%20As%20Worksheet%0A%20%0A'Please%20adjust%20the%20table%20name%20of%20file%20B%20here%0ASet%20wksDateiB%20%3D%20ThisWorkbook.Sheets%20(%22Table1%22)%0A%20%0AWith%20application%0A%20%20%20%20.ScreenUpdating%20%3D%20False%0A%20%20%20%20.EnableEvents%20%3D%20False%0AEnd%20With%0A%20%0A'Please%20adjust%20the%20storage%20path%20%5C%20file%20name%20here!%0A'By%20entering%20the%20character%20%22*%22%20at%20the%20end%20of%20the%20file%20name%20you%20are%20flexible%20with%20the%20file%20name%20assignment%0A'BSP%3A%20FileA%20Rev1.1%20or%20FileA%20Rev1.2%20etc%20...%0AFileA%20%24%20%3D%20Dir%20(%22C%3A%20%5C%20Users%20%5C%20Nikolino%20%5C%20Desktop%20%5C%20FileA%20*%22)%0A'Please%20adjust%20the%20storage%20path%20here!%0AWorkbooks.Open%20%22C%3A%20%5C%20Users%20%5C%20Nikolino%20%5C%20Desktop%20%5C%22%20%26amp%3B%20FileA%20%24%0A'Please%20adjust%20the%20table%20name%20of%20file%20A%20here%0ASet%20wksDateiA%20%3D%20Workbooks%20(FileA%20%24).%20Sheets%20(%22Table1%22)%0A%20%0AWith%20wksFileB%0A%20%20%20%20lngRow%20%26amp%3B%20%3D%20.Cells%20(.Rows.Count%2C%201)%20.End%20(xlUp)%20.Row%0A%20%20%20%20For%20i%20%3D%201%20To%20lngRow%20%26amp%3B%0A%20%20%20%20%20%20%20%20n%20%3D%20IIf%20(.Cells%20(i%2C%201)%20%26lt%3B%26gt%3B%20%22%22%2C%20Application.Match%20(.Cells%20(i%2C%201)%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20wksFileA.Columns%20(1)%2C%200)%2C%20False)%0A%20%20%20%20%20%20%20%20If%20IsNumeric%20(n)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20.Cells%20(i%2C%202)%20.Value%20%3D%20wksDateiA.Cells%20(n%2C%202)%20.Value%0A%20%20%20%20%20%20%20%20%20%20%20%20blnData%20%3D%20True%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0A%20%20%20%20Workbooks%20(FileA%20%24).%20Close%20False%0AEnd%20With%0A%20%0ASet%20wksDateiA%20%3D%20Nothing%0ASet%20wksDateiB%20%3D%20Nothing%0A%20%0AWith%20application%0A%20%20%20%20.ScreenUpdating%20%3D%20True%0A%20%20%20%20.EnableEvents%20%3D%20True%0AEnd%20With%0A%20%0AIf%20blnData%20Then%0A%20%20%20%20MsgBox%20%22Data%20has%20been%20updated.%22%2C%20VbInformation%2C%20%22INFO%3A%20NEW%20DATA%22%0A%20%20%20%20Else%0A%20%20%20%20MsgBox%20%22No%20data%20was%20found!%22%2C%20VbInformation%2C%20%22INFO%3A%20NO%20DATA%20FOUND%22%0AEnd%20If%0A%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20could%20help%20you%20at%20least%20a%20little.%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20patience%20and%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1877636%22%20slang%3D%22en-US%22%3EBetreff%3A%20Copy%20data%20to%20another%20Workbook%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3EIf%20you%20have%20the%20file%20saved%20in%20a%20SharePoint%20library%2C%20you%20should%20be%20able%20to%20use%20Power%20Automate%20to%20move%20rows%20from%20one%20spreadsheet%20to%20another%20based%20on%20a%20status%20column.%20You%20could%20set%20the%20workflow%20to%20run%20on%20a%20schedule%20that%20looks%20for%20all%20rows%20in%20the%20table%20set%20to%20a%20%22Hide%22%20status%2C%20create%20them%20in%20a%20new%20workbook%20stored%20in%20the%20same%20document%20library%20and%20finally%20loop%20back%20round%20to%20the%20original%20to%20delete.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1879571%22%20slang%3D%22de-DE%22%3ESubject%3A%20Copy%20data%20to%20another%20workbook%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1879571%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F2441%22%20target%3D%22_blank%22%3E%40Duane%20Bergh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20always%20advantageous%20if%20the%20Excel%20version%20and%20the%20operating%20system%20were%20known%20from%20the%20outset%2C%20everyone%20would%20surely%20save%20a%20lot%20of%20time%2C%20me%20too.%3CBR%20%2F%3EI%20can%20understand%20why%20many%20do%20not%20want%20to%20answer%20questions%20that%20do%20not%20have%20precise%20details.%3C%2FP%3E%3CP%3EBut%20I%20think%20getting%20a%20wrong%20answer%20is%20always%20better%20than%20not%20getting%20an%20answer.%3C%2FP%3E%3CP%3EIn%20this%20sense%2C%20please%20ignore%20my%20suggested%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20that%20I%20took%20your%20time%20and%20couldn't%20give%20you%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWish%20you%20a%20nice%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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)

 

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