Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a macro
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a macro
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with a macro
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 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) |