Macro Copy Paste without deleting cell value

[freddie mitchell]

Hi everyone,

Simple question. Is there a way of Macro copying data from one workbook to another without deleting existing data in the latter workbook?

I recognise one workaround would be an insert / add row function, but not sure if there is a concatenating function that I could use? For example I'd like to copy data from Workbook Z, cell B:3 to Workbook Y, cell B:3, without deleting any existing data in Workbook Y, cell B:3?

The context is that I'm building a development tracker, in which multiple people will update a master workbook with their data, but this data could be added to the same cells by multiple people. And so I'd like to avoid data being overwritten each time.


Thank you for all your invaluable help.

 

[SQUIDD]

with multiple people adding data to the master workbook. Assume the code will open the master. Update it and close. But what if multiple people open it at the same time. ?

 

[freddie mitchell]

Hi Squidd,

Hmm, yes it could be the case that two people try to Macro open the Master workbook at the same time. The Master workbook is on our company's SharePoint, would this make it possible for two+ people to Macro open the Master workbook at the same time?

Is it possible to add data to the Master workbook without opening it?

And on a similar topic, can you advise how one finds the copy path for a SharePoint document? I'm having trouble finding it.

Thank you Squidd

 

[SQUIDD]

Hi Freddie

So i really do not know about opening a sharepoint file.

That said that may be a question asked as a seperate thread, although a quick search found this.

open sharepoint file with vba

Regarding updating the file, it is my understanding that yes, you have to open the file to update it.

I would also assume that the simple line of code required to not delete the data would be.

I think more clarity is required regarding the updating of the data, the below will take the cell data and simp[y add more data to the existing.

range("B3") = range("B3") + new_data

As i am unsure how your data is laid out, im finding it difficult to work out how you want people to add the data.

does each user fill out a row

Dave

 

[freddie mitchell]

Hi Squidd,

Thank you for sharing the thread. I had a quick look at this before, but will take a closer look now.

To answer your question, I've posted the two mini sheets below. The first one is the Master Workbook. The second is the submitting Workbook. In this simplified version. The submitting Workbook should press the Commandbutton1 to submit data from cells A3:G3 to the same cells in the Master Workbook, but without deleting any of the existing data already present in the Master Workbook's cells A3:G3.

I can get the submitting Workbook to copy / paste data to the Master Workbook, but not sure how to ensure existing data in the Master Workbook is not lost.

Issue tracker test.xlsm
	A	B	C	D	E	F	G
1	Bulgaria
2			15/09/2021	01/10/2021	15/10/2021	01/11/2021	01/11/2021
3	Relevant political developments:		September 16: New caretaker government announced with Stefan Yanev continuing as Prime Minister and Stoycho Katsarov as Health Minister
Bulgaria

Issue tracker test.Bulgaria.xlsm
	A	B	C	D	E	F	G
1	Bulgaria
2	Testing12345	01/09/2021	15/09/2021	01/10/2021	15/10/2021	01/11/2021	01/11/2021
3	Relevant political developments:		September 16: New caretaker government announced with Stefan Yanev continuing as Prime Minister and Stoycho Katsarov as Health Minister
Bulgaria

 

[SQUIDD]

Ok.

So for example.

Let’s say the master A3:G3 all had the number 100 in.

You want to hit a button the adds the contents of your App file A3:G3 to the master. Let’s say you app data has the word “hello” in them. So your master workbook cells will now read “100hello” after button press?



Dave

 

[freddie mitchell]

Hi Dave,

yes that is exactly the case, you've the hit the nail on the head! . Any thoughts/ideas of whether this is possible, how it could be made?

Thank you,

Freddie

 

[SQUIDD]

Yes, that can be done easy enough.

I will do it for you a bit later if no one else does it beforehand.

 

[SQUIDD]

Hi

So as a very basic example below.

I had a mcaro enabled excel file called APP and another excel file call MASTER on my desktop.
The below code was in a module in the file APP
And in the example below, whatever is in cell A1 in the APP goes into the cell A1 on the MASTER. But The MASTER also retains the previous information in that cell.

So you should be able to build from the the below example to configure how you want the manipulate the data.

However, as is said, i do not know how your data will react with multiple users updating the MASTER file.

I have built a similar thing for myself not using sharepoint, but i had to created a file locking method, that detected if the MASTER file was already open by another user and the data had to wait for the file to be closed before the new data would update.

The way I worked this was as follows.

Multiple people would have a copy of the main APP file. The could all do work on the APP since it was its own individual file.
Then when they was ready to save the data from the APP to the MASTER, they would press the button to do so. The updat of data is incredibly fast.
At this point we checked to see if the file was locked, if it was it would loop until it was unlocked.When it was unlocked we would lock it again, add our data and save, then unlock it again.
This prevented the MASTER from being opened 2 times and prevented possible data conflicts.
But I could not really find that excel have a way to check file lock in the above manor.
So what i done to lock the file was simply create a blank folder in a directory when the updat button was pressed, and it was the existance of that empty folder that we could tell someone else was updating the file, this folder was then deleted after the update process had been completed.

Sub SEND_DATA_TO_EXCEL_MASTER_FILE()
MB = "C:\Users\Dave\Desktop\" 'ENTER DIRECTORY FOR YOU MASTER FILE
MN = "MASTER.XLSX" 'ENTER MSASTER FILE NAME AND EXTENSION
TB = ThisWorkbook.Name 'GETS THE CURRENT WORK NAME
Workbooks.Open Filename:=MB & MN 'OPEN MASTER FILE

Range("A1") = Range("A1") & Workbooks(TB).Sheets("SHEET1").Range("A1") 'DO STUFF
'DO MORE STUFF

Workbooks(MN).Close SaveChanges:=True 'CLOSE AND SAVE THE MASTER WORKBOOK
End Sub

 

[freddie mitchell]

Hi Dave

Thank you so much for this. I'm going try it out later today and let you know how it goes.