|
Keep a record of entries in cells
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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
Keep a record of entries in cells
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 -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
Keep a record of entries in cells
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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Kassie" wrote: 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 -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
Keep a record of entries in cells
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: 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 -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
Keep a record of entries in cells
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! -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Kassie" wrote: 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 -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
Keep a record of entries in cells
Thank you Stefi
-- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Stefi" wrote: 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: 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 -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
Keep a record of entries in cells
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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Kassie" wrote: 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! -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Kassie" wrote: 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 -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
Keep a record of entries in cells
You are welcome! Thanks for the feedback!
Stefi €˛Leanne M (Aussie)€¯ ezt Ć*rta: Thank you Stefi -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Stefi" wrote: 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: 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 -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
Keep a record of entries in cells
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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Don Guillett" wrote: 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 sheetview codecopy\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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Leanne M (Aussie)" wrote in message ... 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Kassie" wrote: 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! -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Kassie" wrote: 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 -- Hth Kassie Kasselman Change xxx to hotmail "Leanne M (Aussie)" wrote: 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 -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
| All times are GMT +1. The time now is 07:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com