Keep a record of entries in cells

[Leanne M (Aussie)]

Hi,

What I am trying to do is when a button is clicked I want the information to
be recorded. I have just used a basic macro to copy and paste and to enter
the date but it does not work.

I think I may need to use more complicated coding and in this case
HELP!!!!!!!!!!!!!!!!!!!!!

This is what I have used for the first part of the process -

Private Sub CommandButton1_Click()
Range("C6").Select
Selection.Copy
Sheets("Sheet3").Select
Range("A3").Select
ActiveSheet.Paste
Range("B3").Select
Sheets("Sheet1").Select
Range("C14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Range("C3").Select
ActiveSheet.Paste
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B4").Select
Sheets("Sheet1").Select
End Sub

Here is what I want to do - (at this stage)
Click button Confirm
Have details from C6 & C14 recorded somewhere (currently a new sheet but not
important) and date stamped.
This will allow me to keep a record of stock taken.

Thanks in advance

 

[Kassie]

Although cumbersome code, it works?
You can reduce it to the following:

Dim wS1, wS2 as Worksheet

Private sub Test()
Set wS1 as = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet3")
wS1.Range("C6").Copy wS2.Range("A3")
wS1.Range("C14").Copy wS2.Range("C3")
wS2.Range("B3") = "=TODAY()"
wS1.Activate
End Sub

 

[Leanne M (Aussie)]

Thank you this works.

How do I now get it to go to the next blank row so that I can continue to
record the data?

Leanne

 

[Stefi]

wS2.Range("B3") = "=TODAY()"

inserts the TODAY function, it'll be updated each day. A time stamp must
remain unchanged! Use

wS2.Range("B3") = Date

Regards,
Stefi


„Kassie†ezt írta:

 

[Kassie]

I would require a lot more info to assist you further! In your example, you
copied from C6 and C14. Where would the next pair be situated - C7 and C15,
or D6 and D14, or what? How many lines do you want to copy, or is this
variable?

Aussies go to the UK, S Africans to Aus, and so it goes on!

 

[Leanne M (Aussie)]

Thank you Stefi

 

[Leanne M (Aussie)]

Hi Kassie,

the next pair would be copied from the same location but they would need to
be copied to A4 & C4. The number of times I need to do this is variable.

The location they are copied from is kind of a template so would be changed
each time.

Hope this makes sense.

Leanne

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Leanne M (Aussie)†ezt írta:

 

[Don Guillett]

I may not be understanding your need but when you DOUBLE click on a cell in
col C, that cell is copied to the next available cell in col A of the
destination sheet, the cell 8 rows down is copied to col C and the date is
inputted in B
To place the code right click on the source sheet>view code>copy\paste this.
All is done from the active sheet with no selections needed or desired. If
you want more than one at a time you can use a loop.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
ar = ActiveCell.Row
With Sheets("sheet11")'Destination sheet name here
dlr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(ar, "c").Copy .Cells(dlr, "a")
Cells(ar + 8, "c").Copy .Cells(dlr, "c")
..Cells(dlr, "b") = Date
End With
End Sub

 

[Leanne M (Aussie)]

Hi,

The formula Kassie provided (with the inclusion of date) works. I have
added this code to a command button.

What I need now is to continue recording this data and retain existing data
- therefore I need the information copied to the next available row on the
destination sheet.

The cells to be copied will always be C6 & C14