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.

 

[freddie mitchell]

Evening Dave,

Just checking in with you about the code. I tried the below, adding in a command button for the APP file, but it came up with error '1004', no file found. Not sure how that happened because I used the 'copy path' button for the master file to ensure I picked the right one and, as you can see below, I used the file on C drive, rather than SharePoint. Any thoughts on this would be greatly appreciated.

Private Sub CommandButton1_Click()

MB = "C:\Users\David12\OneDrive - DavidComputer\Documents\Clients\Excel VBA testing\Issue tracker test.xlsm" 'ENTER DIRECTORY FOR MASTER FILE"
MN = "Issue tracker test.xlsm" 'ENTER MASTER 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

End Sub

 

[SQUIDD]

Hi.

So if you look again. You have the file name and extension in the file path. Remove this. Leaving the last \

See how that goes.

 

[freddie mitchell]

Hi,

Fantastic stuff, the code is running without errors. Two questions.

1. When the APP command button is clicked it only copies data from cell A1 APP workbook into cell A1 APP workbook. The code successfully opens and closes the master workbook, but no data is added to cell A1 master workbook. Do you know why this is/any ideas how to solve this?

2. I'd like to use the phrase x1Down and x1toright in the code, to ensure that all cells with data are successfully copied into the master workbook. Can you offer some suggestions on how this could be integrated into your code? I made several attempts but all ended with errors.

Thank you again for all your help,

Freddie

 

[SQUIDD]

Hi Freddie

So i just tested it.

Create a macro enabled workbook called APP.(XLSM)
Put the code in a module in this workbook.
Save it.
Create another excel file called MASTER, make sure its just a XLSX file.
Obviously also beeds a sheet called SHEET1.
save and close this.
Open up your APP file, enter something into A1, run the code, then run the code again.
Your master file should now have whatever you entered into A1 originally twice.

Or at least it did for me.

But thinking further, i guess fo some reason the MASTER file is not the active workbook after its opened.

So try the below instead to firm it up.

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

Workbooks(MN).Sheets("SHEET1").Range("A1") = Workbooks(MN).Sheets("SHEET1").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

OR this approach making sure its active 1st.

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
Workbooks(MN).Activate
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

Regarding point 2, could you elaborate further.

Dave

 

[freddie mitchell]

Hi Dave,

That's fantastic, thank you. I'm using the second solution you offered and it's working well. On point 2, I found a way of tackling it.

Thank you again for your help, certainly couldn't have done it without you. #


Freddie

 

[SQUIDD]

Hi Freddie.

Great. Good luck on the rest of your project.

Dave.