View Full Version : Solved: Moving Data
murto
07-18-2009, 10:04 PM
I have a table of data that the first line becomes outdated and new data is added to the end.
My range of data is for example D14 to E18. When I update D14 & E14 are replaced by the data in E14 & E15, In effect the bottom 4 lines of data are moved up 1 row in each update leaving the row clear to accept new data.
My question is how can I automate moving 4 rows of data to move up 1 row by clicking on a cell marked "Update".
This will be a new area of "Excel" manipulation for me, I have tried a command as follows [ =move (d15;e18,d14;e17]. This represents my train of thought at the moment.
Thank you for reading this and any assistance will be greatly appreciate, thank you.
Regards
murto
lucas
07-19-2009, 06:38 AM
Moved to appropriate forum. Please post your questions in the help forums, not in announcements.
mdmackillop
07-19-2009, 07:59 AM
Place this in the Worksheet Module. Double Click on a cell containing "Update"
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Value = "Update" Then Call Update
End Sub
Private Sub Update()
Range("D14:E14").Delete shift:=xlUp
Range("D18:E18").Insert shift:=xlDown
End Sub
murto
07-20-2009, 03:03 AM
Thank you for your assistance it is greatly appreciated. It is what I am looking to achieve, but I will demonstrate my ignorance sa I have only used functions in my spreadsheets to date. Also I am using version 2000.
Was able to attach a copy, hope that it is usable.
Many Thanks for your patience and help,
Regards,
Murdo
Simon Lloyd
07-20-2009, 04:35 AM
Follow these instructions to add the code to your workbook
How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S
murto
07-21-2009, 01:56 AM
Thank you to all who looked and contributed. I will use this answer when I have time enter the macro. I will post a reply soon and let all know how it works.
Many thanks to all,
Murdo
murto
07-25-2009, 03:22 AM
The macro written MDMackillop works very well, I tried typing in the macro, but it didn't work. I then copied as per the protocol outlined by Simon Lloyd. Don't really understand why it didn't work when I typed it in, I have tried to name it for reference sake but can't do that, I don't know if it really matters.
I would like to mark this as solved.
Thank you all for your generous assistance,
Murdo
mdmackillop
07-25-2009, 03:25 AM
I have tried to name it for reference sake
Can you clarify what you are trying to do?
murto
07-25-2009, 03:30 AM
I was trying to give it a name to be able to recall it from a list of other macros in the future. Not to sure if I need to or not?. I am just used to naming files etc and thought that I would have to the same with macro's.
Thank you,
Murdo
mdmackillop
07-25-2009, 03:50 AM
If you are looking to create a code library have a look at Johnske's (http://www.vbaexpress.com/kb/getarticle.php?kb_id=1054) and XL-Dennis (http://www.excelkb.com/article.aspx?id=10229)items
Aussiebear
07-25-2009, 04:50 AM
Murdo, it's nearly always best to copy and paste the code provided as this eliminates the typo's in translating the code from the forum screen to your VBE screen. You can give me a ring if you want to discuss naming procedures further.
Simon Lloyd
07-25-2009, 06:01 AM
Murto, the only macro you would be able to call would be "Update" if you remove the word "Private", you cannot remove the "Private" from the Before_Doubleclick as it is a worksheet event macro, that is to say when a double click happens the code is automatically run.
Hope that helps.
murto
07-26-2009, 03:24 AM
Many thanks to all who contributed to the forum. Greatly appreciated.
Regards,
Murdo
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.