Solved: Moving Data

[murto]

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]

Moved to appropriate forum. Please post your questions in the help forums, not in announcements.

[mdmackillop]

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]

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]

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]

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]

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]

I have tried to name it for reference sake
Can you clarify what you are trying to do?

[murto]

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]

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]

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]

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]

Many thanks to all who contributed to the forum. Greatly appreciated.

Regards,

Murdo