Nov 11 2020 08:53 AM
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 :)
Nov 11 2020 09:55 AM
'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)
Nov 11 2020 10:09 AM
@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.
Nov 12 2020 01:01 AM
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)
Nov 12 2020 01:25 AM
@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