copy and paste from another excel workbook on a shared folder and back to same folder

Question

Please bare with me for my below problem I have been working on this for a 3 months and I cant get my head around it.

I have to explain the whole project so that you can understand what I want my code to do:

I have created a user form which is data entry and it will be used by 3 users at the same time each user on a pc has same excel workbook "ENTRY APPLICATION" and data is entered on a sheet called "NEW ROUND" each user data entries have a serial number that starts with 1 - 1000 for example. and another workbook on a shared folder which is there to have all data entered by 3 users be copied and pasted on the shared workbook "DATABASE", then the data collected on the "DATABASE" to be copied again and pasted on same work book "ENTRY APPLICATION" for the users but in another sheet so that it is mirrors to the shared workbook for the user while sorting so that serial number for the data is sorted correctly for each user, fot that I have same workbook for the 3 users but each one just changed the range for them so that their data be copied on a range so that they dont clear other user data entries, for example : user 1 the paste range A1:N2000, user 2 the paste range is A2001:N4000, user 3 paste range is A4001:N6000 then they all get sorted out when pasted again in their workbook which is "DATA APPLICATION" with the user form.

"DATABASE" workbook which is the shared that all collected data is in it , to prevent duplication entries from users (which is in a different module ) but for now my struggle is I'm trying to have that done by less time and more efficient so that I dont have to use the screenupdate and open activate save close workbooks all the time, which can make work slow and might crash.

I have come to read a great thread here now about Parent Object which apparently save great time and errors for my same needs, but I have no idea what so ever how to reflect that on my userform workbook and how to adjust my code.

please help me adjust my code , hope that I have explained it correctly.

Sub DATA_BASE_ARCHIVE_FullArchive()

Application.ScreenUpdating = False

Windows("ENTRY APPLICATION.xlsm").Activate
Sheets("NEWROUND").Select
Range("A1:N2000").Select

Selection.Copy

Workbooks.Open filename:= _
    "\\2-2023\DATABASE.xlsm"
Windows("DATABASE.xlsm").Activate
Range("A2001").Select
Sheets("FullArchive").Paste
Cells.Select
Range("A2001").Activate
Application.CutCopyMode = False
Selection.Copy


Windows("ENTRY APPLICATION.xlsm").Activate
Sheets("ARCHIVE").Select

Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("ARCHIVE").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ARCHIVE").Sort.SortFields.ADD Key:=Columns("L:L") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("ARCHIVE").Sort.SortFields.ADD Key:=Columns("A:A") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("ARCHIVE").Sort
    .SetRange Columns("A:P")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


Windows("DATABASE.xlsm").Activate
ActiveWorkbook.Save
ActiveWindow.Close
Application.CutCopyMode = False
Windows("ENTRY APPLICATION.xlsm").Activate
Sheets("FORM").Select
End Sub

please forgive my complicated explanation, but the thing I am trying to do has already complicated me enough ! so please help. thank you.

my edited code according to the first answer I received from @stringeater. please check it and help me what to adjust next. Im just getting an error now in setwbkDATABAS = Nothing

    Sub DATA_BASE_ARCHIVE_FullArchive()

Dim rngNEWROUND As Excel.Range
Dim arrNEWROUND As Variant
Dim wbkDATABASE As Excel.Workbook
Dim rngDataTarget As Excel.Range
Dim rngDataSource As Excel.Range
Dim varData As Variant
Dim rngArchive As Excel.Range


Application.ScreenUpdating = False

Set rngNEWROUND = ThisWorkbook.Sheets("NEWROUND").Range("A1:N2000")

arrNEWROUND = ThisWorkbook.Sheets("NEWROUND").Range("A1:N2000")

Set wbkDATABASE = Workbooks.Open(filename:="E:\DELEGATION APPLICATION SAMPLE\2-2023\DATABASE.xlsm")

Set rngDataTarget = wbkDATABASE.Sheets("FullArchive").Range("A2001")

Set rngDataTarget = rngDataTarget.Resize(UBound(arrNEWROUND, 1), UBound(arrNEWROUND, 2))

rngDataTarget.Value = arrNEWROUND 

Set rngDataSource = rngDataTarget.Worksheet.Range("A2001")

varData = rngDataSource.Value 
wbkDATABASE.Save
wbkDATABASE.Close

setwbkDATABASE = Nothing '(and Im getting error here)

Set rngArchive = ThisWorkbook.Sheets("ARCHIVE").Range("A1") 'reference range

rngArchive.Value = varData   
   
   
ThisWorkbook.Worksheets("ARCHIVE").Sort.SortFields.Clear
ThisWorkbook.Worksheets("ARCHIVE").Sort.SortFields.ADD Key:=Columns("L:L"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ThisWorkbook.Worksheets("ARCHIVE").Sort.SortFields.ADD Key:=Columns("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ThisWorkbook.Worksheets("ARCHIVE").Sort
      .SetRange Columns("A:P")
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
End With

Application.ScreenUpdating = True

Sheets("FORM").Select

End Sub

It is not clear to me what means Parent Object. Please provide the link to that article. In your code you are using a Activate - Select - Copy - Activate - Select - Paste pattern. That's how you would do it manually. But VBA know more elegant ways. Maybe it's about this topic. — Stringeater, May 24 '23 at 20:04
https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba this is the thread with parent object Im talking about — Funny Memo Ms, May 24 '23 at 20:09
exaclty I want a more non human approach to be faster and more efficient — Funny Memo Ms, May 24 '23 at 20:10
I'm trying to translate your code into a referencing code. Something ist not clear. You have 3 workbooks: (1) ENTRY APPLICATION.xlsm, (2) DATABASE-2-2023.xlsm", (3) "DATABASE.xlsm". Correct? Which one is used as a database? And is your code in ENTRY APPLICATION.xlsm? — Stringeater, May 24 '23 at 20:44
As a general rule of thumb.. you should not be using `Select` or Activate or active workbook/worksheet unless you are automating the UI for the user. For moving data around, work with the fully qualified worksheet. For example, When you open the workbook, you get a workbook object returned. You can access worksheets from that workbook directly and not the active workbook. Also, just use Range.Copy(Destination). e.g `MyRange.Copy(destinationWorksheet.Cells(1,1))` (Recording a macro will not always yield the best code) — GisMofx, May 26 '23 at 01:17
Just key a space after the "set " . ie. `setwbkDATABASE = Nothing ` should be `set wbkDATABASE = Nothing ` *(Set wbkDATABASE = Nothing )* — Oscar Sun, May 26 '23 at 17:58
yes i noticed and it got fixed. thank you so much all... I would like to know If I want to add to save a sheet of this workbook as a new excel file with over write , is this code correct? and what should I add to overwrite? I want to add it to the code above. ThisWorkbook.Worksheets("ARCHIVE").SaveAs filename:="E:\ALL DELEGATIONS 2-2023" & ".xls" — Funny Memo Ms, May 26 '23 at 19:59
ofcourse this will happen to save as and over write the old one maybe every 2 minutes from each user , and we might need to open it . so does that work? — Funny Memo Ms, May 26 '23 at 20:00
Do you want to save just 1 Worksheet without any VBA inside? Do you mean xls or xlsx? I'm travelling and I'll send you a solution when I'm back. — Stringeater, May 26 '23 at 21:11
You can refer to my updated answer. I believe @Stringeater will come up with a nice solution — Oscar Sun, May 27 '23 at 05:01
yes I want just 1 sheet to be saved in a specific location and everytime it should over write it self, but It will be opened and closed like a user might keep opening the new saved excel file, — Funny Memo Ms, May 27 '23 at 10:15
Please try: `ThisWorkbook.Worksheets("ARCHIVE").Copy 'copy archive sheet to a new workbook If Dir("E:\ALL DELEGATIONS 2-2023.xlsx") <> "" Then 'skip if previous archive file doesn't exist Kill "E:\ALL DELEGATIONS 2-2023.xlsx" 'delete previous archive file End If ActiveWorkbook.Close SaveChanges:=True, Filename:="E:\ALL DELEGATIONS 2-2023.xlsx" 'save and close archive workbook` — Stringeater, May 27 '23 at 14:52
Worksheet.SaveAs doesn't work as expected: it saves the whole workbook instead of a single sheet. So you copy and then save and close the copy. And there is no Overwrite option. Deleting the old file before saving does the same. — Stringeater, May 27 '23 at 14:58
worked thank you so much but when second time it says permission denied on the kill code, any suggestion? — Funny Memo Ms, May 27 '23 at 15:51
@Stringeater Why not use the [FileSystemObject](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object) to do these? Just like my answer mentioned at the end. — Oscar Sun, May 27 '23 at 18:18
@Oscar Sun in your method u mentioned to save it manually, ofcourse we can do that, but in my case users are entering data every couple of minutes so instead of repeating the save as sheet task, I wanted to include that in the code so that it automatically save the ARCHIVE sheet as an external workbook. because a person will be viewing the entries from that external saved excel sheet, — Funny Memo Ms, May 27 '23 at 18:36
so instead of viewing the DATABASE excel sheet which is saving and closing every couple of minutes I need another one just to be updated and saved so that when the person opens it it doesnt get closed because of the code. — Funny Memo Ms, May 27 '23 at 18:36
Using [FileSystemObject](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/copyfile-method) and [Workbook SaveAs method](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas) is run by VBA code, not done manually, In which part of the code do you want to save the current workbook? or at what time? I'd like to give you a snippet to try it out. Or you can wait for @Stringeater to achieve them. Perhaps you can also try to do it yourself according to the reference material I gave you, it is better. — Oscar Sun, May 28 '23 at 06:23
I can't figure out why you get permission denied. Remove the Kill line. Then you should get prompted to overwrite. Can you overwrite without error? I haven't got any experience with FileSystemObject. You can follow the link given by Oscar Sun or google for a tutorial. — Stringeater, May 29 '23 at 09:00

Stringeater · Answer 1 · 2023-05-24T21:26:59.750

I have commented your code lines out and put my lines underneath so that you can see how to "translate" your code. You could shorten the code even more but it might not become more understandable.

Sub DATA_BASE_ARCHIVE_FullArchive()
  
        'declarations
  Dim rngNewRound As Excel.Range    'range object in ThisWorkbook
  Dim arrNewRound As Variant        '2-dim array with content of range
  Dim wbkDatabase As Excel.Workbook 'DATABASE object
  Dim rngDataTarget As Excel.Range  'target range in database
  Dim rngDataSource As Excel.Range  'source range in database
  Dim varData As Variant            'cell content from database
  Dim rngArchive As Excel.Range     'target range on sheet ARCHIVE

  Application.ScreenUpdating = False 'don't forget ... = True at the end
    
  'Windows("ENTRY APPLICATION.xlsm").Activate
  'Sheets("NEWROUND").Select
  'Range("A1:N2000").Select
        'supposing that this VBA code is contained in ENTRY APPLICATIN.xlsm
  Set rngNewRound = ThisWorkbook.Sheets("NEWROUND").Range("A1:N2000") 'reference range
  'Selection.Copy
  arrNewRound = rngNewRound.Value 'save the content of the range in an array
    
  'Workbooks.Open Filename:="\\2-2023\DATABASE-2-2023.xlsm"
  Set wbkDatabase = Workbooks.Open(Filename:="\\2-2023\DATABASE-2-2023.xlsm")
        'is DATABASE.xlsm = DATABASE-2-2023.xlsm? Please clarify!
  'Windows("DATABASE.xlsm").Activate
  'Range("A2001").Select
  Set rngDataTarget = wbkDatabase.Sheets("FullArchive").Range("A2001") 'set top-left corner
        'resize the range so that it matches the size of the array
  Set rngDataTarget = rngDataTarget.Resize(UBound(arrNewRound, 1), UBound(arrNewRound, 2))
  'Sheets("FullArchive").Paste
  rngDataTarget.Value = arrNewRound 'insert array to range
  
  'Cells.Select
  'Range("A2001").Activate
  Set rngDataSource = rngDataTarget.Worksheet.Range("A2001") 'new range in same worksheet
  'Application.CutCopyMode = False
  'Selection.Copy
  varData = rngDataSource.Value     'save range value to single variable
  wbkDatabase.Save                  'save the database
  wbkDatabase.Close                 'close the database
  Set wbkDatabase = Nothing         'release memory
  
  'Windows("ENTRY APPLICATION.xlsm").Activate
  'Sheets("ARCHIVE").Select
  'Range("A1").Select
  Set rngArchive = ThisWorkbook.Sheets("ARCHIVE").Range("A1") 'reference range
  'ActiveSheet.Paste
  'Application.CutCopyMode = False
  rngArchive.Value = varData        'insert single variable to cell A1
  
  'The sorting is probably ok. Please use ThisWorkbook instead of ActiveWorkbook
  ActiveWorkbook.Worksheets("ARCHIVE").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("ARCHIVE").Sort.SortFields.Add Key:=Columns("L:L"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  ActiveWorkbook.Worksheets("ARCHIVE").Sort.SortFields.Add Key:=Columns("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("ARCHIVE").Sort
      .SetRange Columns("A:P")
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  End With
  
  Windows("DATABASE.xlsm").Activate
  ActiveWorkbook.Save
  ActiveWindow.Close
  Application.CutCopyMode = False
  Windows("ENTRY APPLICATION.xlsm").Activate
  
  Application.ScreenUpdating = True 'otherwise you may not see the updated result
  
  Sheets("FORM").Select

End Sub

thank you so much for all the effort u put in that, I will add my code to see what I did wrong because Im having error with wbkDATABASE. and yes DATABASE is same as DATABASE-2-2023 I just forgot to amend it in my code but now I did. — Funny Memo Ms, May 26 '23 at 12:45
I dont know how much I can thank you it worked amazing thank you from the heart. just last question. would that work normally with 3 users using same code same time? pasting data on same DATABASE.xlsm? ofcourse I will change range so that they dont paste over each other. — Funny Memo Ms, May 26 '23 at 14:03
I'm happy it worked. Of course, several users can run the code but not at the same time because the open DATABASE.xlsm would be locked. But saving the data takes very little time. — Stringeater, May 26 '23 at 20:50
What you are doing is a typical task for a database server. But there's a long way to go. You can learn it if you are interested. — Stringeater, May 26 '23 at 20:54
Yes i am learning it and researching it. thank you so much dear — Funny Memo Ms, May 27 '23 at 10:12
I will try it out on 3 pcs for 3 users this monday , I hope that It will not make errors while using it together and opening and saving together, if it will only be like a wait time with no errors or bug the all would be good. — Funny Memo Ms, May 27 '23 at 10:22

Oscar Sun · Accepted Answer · 2023-05-27T05:00:36.413

Since I don't have your sample and all the codes, I can only imagine. Please try using the Range object instead of Selection, unless you want to see it running when it is running, otherwise, there is no need to refresh the screen. In your code :

Application.ScreenUpdating = False

ScreenUpdating = False and you also said

so that I don't have to use the screenupdate and open activate save close workbooks all the time. So that I don't have to use the screenupdate and open activate save close workbooks all the time, which can make work slow and might crash.

also :

want a more non human approach to be faster and more efficient

So it is ideal to use Range instead of Selection to implement. This is the same principle as in Word and PowerPoint VBA. Unless you need to refresh the screen or something needs to have the focus to do the operation, you should not actually use Selection. I think @Stringeater 's answer upstairs has already done this, so he commented out your Selection statements and used the Range object below to execute it.

If you still don't understand clearly or have questions, please let us know again.

20230527 12:59 (CST)

If you just want to save the results as an Excel file (with or without the code), it is easy to do. The simplest way is to save it locally and then overwrite the destination file with a CopyFile method. If local saving is not possible and only save-as is allowed, then the SaveAs method in ExcelVBA will have to be used. It is more complicated to pay attention to the Multitasking reading conflict. I'm sure @Stringeater will come up with a nice solution, just like he did before. Thank you

copy and paste from another excel workbook on a shared folder and back to same folder

2 Answers2

20230527 12:59 (CST)

Linked