UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Copy/ Paste, But Not Overwrite, Office 2013    
 
   
kenwood1
post Mar 13 2019, 07:30 AM
Post#1



Posts: 503
Joined: 11-July 09



Hi all,

I am facing with a problem that take me several days and still not solve. I have Workbook and this workbook is shared in the network. So everybody can use with there computer to order the items in (Sheet1 Export) with a command button1. So the command button1 in Export will copy all data to (Sheet2 "Spedition").

I have tried with differents PC in the network to make an order, but this have a conflict because in Row of "Spedition-Sheet2" they containt already some data and a message errors pop up ask which one or users you want to keep the data, data from PC3 in the network or your?

Is there anyway to not overwrite the data and not pop up message data conflic with others User, but they just put somewhere in the sheet2 Spedition where they can find empty rows? Also in Sheet2 Spedition, after taking order the user can errase between the rows and this Sheet2 is always open by the receiver. So it makes difficult also for me to do the task (like updating or .....

I am very appreciated for any help.

Thanks in advance.




'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long


'Set variables for copy and destination sheets
Set wsCopy = Workbooks("Test1.xlsm").Worksheets("Export")
Set wsDest = Workbooks("Test1.xlsm").Worksheets("Spedition")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row



'Copy range to clipboard
Workbooks("Test1.xlsm").Worksheets("Export").Range("A4:F20").Copy

'PasteSpecial to paste values, formulas, formats, etc.
Workbooks("Test1.xlsm").Worksheets("Spedition").Range("A" & lDestLastRow).PasteSpecial Paste:=xlPasteValues


Range("A4:F20").Clear

Application.Sheets("Spedition").Select
Application.Sheets("Export").Select



ActiveWorkbook.Save


ThisWorkbook.Save

Application.ScreenUpdating = True
This post has been edited by kenwood1: Mar 13 2019, 07:33 AM
Go to the top of the page
 
Jaiket
post Mar 15 2019, 04:37 PM
Post#2



Posts: 385
Joined: 3-May 17
From: France


Hi Kenwood,
I don't know Excel very well. Is the aim of this file to receive multiple orders and then combine them?
Each user can make an order. So there may be multiple orders. All the orders from Sheet 1 are written to Sheet2 when the command button "Export" is clicked.
It looks like you are trying to store several orders in a place where there is room for only one.
You have mentioned a few problems - the pop-up, Excel wanting to erase data, difficulty updating, and finding a way to select cells to house the next order.

Sorry if I might have misunderstood, I am not used to your way of using English. I cannot understand this:
QUOTE
Also in Sheet2 Spedition, after taking order the user can errase between the rows and this Sheet2 is always open by the receiver.

'After taking order' - do you mean after the command button is clicked and the order is copied from Sheet 1 to Sheet 2?
'the user can erase between the rows' - Sheet 2, I guess? What will he erase - a whole order? Between which rows?
'is always open by the receiver.' - Do you mean "User on PC3 erases some rows on Sheet 2. Sheet 2 is still open on PC1 but the rows erased by PC3 are still visible on PC1?
I hope I got that wrong..

It seems important to understand About the shared workbook feature Are you using this?

It doesn't seem right to me that you just append data in the next available row, and getting users to manually erase rows seems a pity too.
I would also aim to get the orders combined automatically, but you seem to be doing that manually.
This post has been edited by Jaiket: Mar 15 2019, 04:42 PM

--------------------
Life is too short to drink bad wine.
Go to the top of the page
 
Jaiket
post Mar 16 2019, 01:56 AM
Post#3



Posts: 385
Joined: 3-May 17
From: France


You are telling Excel to write to a row.

Excel thinks this row is empty, but it is not. So the pop-up comes.

Why does Excel think this row is empty?
Because it doesn't know someone else has written to it.

So you need to make sure it does know, at the beginning of your function.

I don't know which is best : reload, refresh, or something else.

So BEFORE you export, you must ensure Sheet2 is up-to-date with all saved changes by all users.
This post has been edited by Jaiket: Mar 16 2019, 01:56 AM

--------------------
Life is too short to drink bad wine.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 07:27 PM