Macro Copy Paste without deleting cell value

freddie mitchell

New Member
Joined
Apr 14, 2011
Messages
43
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

New Member
Joined
Apr 14, 2011
Messages
43
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.

VBA Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
2,038
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 14, 2011
Messages
43
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

Well-known Member
Joined
Jan 2, 2009
Messages
2,038
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.

VBA Code:
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.

VBA Code:
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
 
Solution

freddie mitchell

New Member
Joined
Apr 14, 2011
Messages
43
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

Well-known Member
Joined
Jan 2, 2009
Messages
2,038
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Freddie.

Great. Good luck on the rest of your project.

Dave.
 

Forum statistics

Threads
1,176,145
Messages
5,901,601
Members
434,906
Latest member
butterthemuffin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top