Macro for updating diary entries

[Difflam88]

Hi everyone,

Having a bit of an issue finding the solution here.

I'm trying to create a simple diary

Say I have a 2 sheet excel document

Sheet 2 contains

A B
Name Comment
John
July

Sheet 1 contains

A1 a drop down box made from Sheet 1
A2 a date field with todays date
A3 a comment box
A4 a submit button.
A5 code that will show the existing comments in Sheet 2 .... written as =VLOOKUP(A1, 'Sheet2!A2:B6,2,FALSE)
A6 code that will consolidate the existing comments in A5 with A2 & A3 .... written as =CONCATENATE(A5," - ",A2," - ",A3 " - ")

So what I need is a macro that when clicked will copy the text data from A6 of Sheet 1 and paste into the B column of Sheet 2 (depending on which name was selected from the drop down menu in Sheet 1).

I figure the way to do this would be;

*Record*
Click A1 in Sheet 1
Copy
Click Sheet 2
Click 'find'
Paste
Click enter
Click exit
Click right direction arrow once
Click paste
Click Sheet 1
*End recording*

But this doesn't seem to work as the copy and paste into the find search function doesnt seem to record at all. Any ideas on how I may do this?

[antoka05]

You could use this code:

Please Login or Register  to view this content.

Regards,
Antonio

[Difflam88]

Thanks Antonio,

These macro codes are a little difficult to get my head around so I might have to do some reading on how to interpret what you have written here.

I'm a quick learner and I think I will be able to understand this with a day of learning macro code. Any suggestion of where to start?

[antoka05]

I tried to add other comments so it's a little more easy to understand.

Please Login or Register  to view this content.

I hope that new comments helped a little more you.

Best regards,
Antonio

[Difflam88]

Thanks Antonio,

That is very helpful actually, i think i've learnt quite a bit from these additional comments, i can't thank you enough!

Just one quick question, the 'Sheets' part. My workbook has about 10 different sheets. Now i can't remember the order they were created in but I do have them in a specific order now.

So sheet 1 is now technically sheet 2 (in terms of the order of the worksheet panels at the bottom of the page left to right) and I have called this Submission form
Sheet 2 is now sheet 6 (in terms of the order of the worksheet panels at the bottom of the page left to right) and I have called this 'Data Sheet'

So do i change this first part to to read;

Sub Test()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim myName As String, myRange As Range
Dim myText As String

With ThisWorkbook
Set sh1 = .Sheets(2)
Set sh2 = .Sheets(6)
End With

OR

Sub Test()
Dim sh1 As 'Submission form'!, sh2 As 'Data Sheet'!
Dim myName As String, myRange As Range
Dim myText As String

With ThisWorkbook
Set sh1 = .Sheets(2)
Set sh2 = .Sheets(6)
End With

Also the data on the second page is now in a column 90 so i assume i just change the offset from 1 to 89. Is that correct?

[antoka05]

You can referene a sheet using a number ( sheets(1)... sheets(10)) or using the sheet's name, like sheets("data sheet"), sheets("summary sheet") and so on that it's more easy to use.

To reference the 90th column you can rightly change the offset in 89 or use the 'Cells' instruction that requires row and column, like cells (number of row, number of column):

Please Login or Register  to view this content.

Please, when you paste the code on this forum, select the code and press the button with '#' so the code has the right tag.

Regards,
Antonio