Help with a macro
in [General]
|
From: NEHicks on 15 Apr 2010 12:16 I have a form that people enter daily information in. I need to be able to retain the data each day. So, my thought was to link cells to a second worksheet. How do I make the 2nd worksheet switch to the next row after the last column is filled in so that the next day has a new row to take data in?
From: joel on 15 Apr 2010 12:28 I wouldn't use linking if you are going to write a macro. You should put the date in column A and have the macro compare the todays date against the date in the last row. If the date matches add the data to the end of the row, otherwise, put the date in column A and the data in column B. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=195903 http://www.thecodecage.com/forumz
From: Jim Thomlinson on 15 Apr 2010 12:40 So you want a data input form on one sheet and a database on the other. that sounds like a good way to proceed. Taht being said you are not so much looking to link the data form to the database but rather allow the end user to append data to the database base on their form inputs. My recommendation would be to add a button to the input form that copies the relevant values to the next available row on the database sheet. The code could be similar to this... Private Sub CommandButton1_Click() Dim rng As Range Set rng = Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) With rng .Value = IIf(Application.IsText(.Offset(-1, 0).Value), 0, _ .Offset(-1, 0).Value) + 1 'index number .Offset(0, 1).Value = Sheet1.Range("A1").Value .Offset(0, 2).Value = Sheet1.Range("A2").Value End With End Sub -- HTH... Jim Thomlinson "NEHicks" wrote: > I have a form that people enter daily information in. I need to be able to > retain the data each day. So, my thought was to link cells to a second > worksheet. How do I make the 2nd worksheet switch to the next row after the > last column is filled in so that the next day has a new row to take data in?
From: Dianne on 15 Apr 2010 12:49 NEHicks;700232 Wrote: > I have a form that people enter daily information in. I need to be able to > retain the data each day. So, my thought was to link cells to a second > worksheet. How do I make the 2nd worksheet switch to the next row after the > last column is filled in so that the next day has a new row to take data in? ------------------------------- The best way would be to use VBA code. Who designed the form? -- Dianne ------------------------------------------------------------------------ Dianne's Profile: http://www.thecodecage.com/forumz/member.php?u=1755 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=195903 http://www.thecodecage.com/forumz
From: Gord Dibben on 15 Apr 2010 13:06 Very little detail on form layout and number of cells and addresses involved so hard to tailor something. Example code.................. Here is sheet event code using one cell only.........B5 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" Then Application.EnableEvents = False j = (Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1) Sheets("Sheet2").Cells(j, 1) = Target.Value Application.EnableEvents = True End If End Sub Right-click on Sheet1 tab and "View Code". Copy/paste the above into that module. Type something in B5 and it will be copied to Sheet2 in Column A Type again in B5 and will be copied below previous data in Sheet2 Gord Dibben MS Excel MVP On Thu, 15 Apr 2010 09:16:01 -0700, NEHicks <NEHicks(a)discussions.microsoft.com> wrote: >I have a form that people enter daily information in. I need to be able to >retain the data each day. So, my thought was to link cells to a second >worksheet. How do I make the 2nd worksheet switch to the next row after the >last column is filled in so that the next day has a new row to take data in?
|
Pages: 1 Prev: trouble with shared workbook Next: Printing selected pafes in Excel 2007 |