Copying data from one sheet to another

[fish0321]

HI,

I am trying to create a tracker to tack events. I am having a bit of trouble copying data from the event page to the event database page. When I push the button on the event page, I would like to have the data transferred to to table in the event database to the next available row. I have included an example spreadsheet. Thanks for you help in advance.

Event Tracker.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S
1		Event
2
3
4
5			Date:	1/1/2023		test 1:	ab		Hrs:	6
6
7			test 2:	mn		Test 3:	Liasion
8
9			Comments:
10			Thank You for your help, it much appreciated!
11
12
13
14
15
16		Test 4 (a,b,c)
17					Mike	Steve	Steve	Scott
18
19
20
21
22
Event

Cells with Data Validation
Cell	Allow	Criteria
N5	List	=Data!$A$2:$A$6
Q5	List	=Data!$B$2:$B$11
K7	List	=Data!$C$2:$C$15
N7	List	=Data!$D$2:$D$10
L17:O17	List	=Data!$E$2:$E$20

Event Tracker.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Test 1	Test 2	Test 3	Hrs	Test 4	Test 4a	Test 4b	Test 4c	Comments
2
3
4
5
6
7
8
Event Database

 

[dmt32]

Hi,
Assuming that your Database is an Excel Table (not a range) then see if following will do what you want

Place code in your data entry form worksheet code page

Private Sub CommandButton1_Click()
    Dim rngEntry            As Range, cell As Range
    Dim i                   As Long
    Dim arr()               As Variant
    Dim tbl                 As ListObject
    Dim NewRecord           As ListRow
  
    'data entry input range
    Set rngEntry = Me.Range("K8,N8,K10,N10,Q8,L20:O20,J13")
    'database table
    Set tbl = ThisWorkbook.Worksheets("Event Database").ListObjects(1)
  
    'check form not blank
    If Application.CountA(rngEntry) > 0 Then
      
        'size array
        ReDim arr(1 To rngEntry.Cells.Count)
      
        'add entry to array
        For Each cell In rngEntry.Cells
            i = i + 1
            arr(i) = cell.Value
        Next cell
      
        'Add New row to the table
        Set NewRecord = tbl.ListRows.Add(AlwaysInsert:=True)
        'post array to table
        NewRecord.Range(1).Resize(, UBound(arr)).Value = arr
      
        'clear entry form
        rngEntry.Value = ""
        'inform user
        MsgBox "New Record Saved To Database", 64, "New Record"
    End If
  
End Sub

24-01-2023.xls
	A	B	C	D	E	F	G	H	I	J
1	Date	Test 1	Test 2	Test 3	Hrs	Test 4	Test 4a	Test 4b	Test 4c	Comments
2	01/01/2023	ab	mn	Liasion	6	Mike	Steve	Steve	Scott	Thank You for your help, it much appreciated!
Event Database

Change name of your commandbutton as required

Dave

 

[fish0321]

Hi,
Assuming that your Database is an Excel Table (not a range) then see if following will do what you want

Place code in your data entry form worksheet code page

Private Sub CommandButton1_Click()
    Dim rngEntry            As Range, cell As Range
    Dim i                   As Long
    Dim arr()               As Variant
    Dim tbl                 As ListObject
    Dim NewRecord           As ListRow
 
    'data entry input range
    Set rngEntry = Me.Range("K8,N8,K10,N10,Q8,L20:O20,J13")
    'database table
    Set tbl = ThisWorkbook.Worksheets("Event Database").ListObjects(1)
 
    'check form not blank
    If Application.CountA(rngEntry) > 0 Then
     
        'size array
        ReDim arr(1 To rngEntry.Cells.Count)
     
        'add entry to array
        For Each cell In rngEntry.Cells
            i = i + 1
            arr(i) = cell.Value
        Next cell
     
        'Add New row to the table
        Set NewRecord = tbl.ListRows.Add(AlwaysInsert:=True)
        'post array to table
        NewRecord.Range(1).Resize(, UBound(arr)).Value = arr
     
        'clear entry form
        rngEntry.Value = ""
        'inform user
        MsgBox "New Record Saved To Database", 64, "New Record"
    End If
 
End Sub

24-01-2023.xls
	A	B	C	D	E	F	G	H	I	J
1	Date	Test 1	Test 2	Test 3	Hrs	Test 4	Test 4a	Test 4b	Test 4c	Comments
2	01/01/2023	ab	mn	Liasion	6	Mike	Steve	Steve	Scott	Thank You for your help, it much appreciated!
Event Database

Change name of your commandbutton as required

Dave

Hi,
Assuming that your Database is an Excel Table (not a range) then see if following will do what you want

Place code in your data entry form worksheet code page

Private Sub CommandButton1_Click()
    Dim rngEntry            As Range, cell As Range
    Dim i                   As Long
    Dim arr()               As Variant
    Dim tbl                 As ListObject
    Dim NewRecord           As ListRow
 
    'data entry input range
    Set rngEntry = Me.Range("K8,N8,K10,N10,Q8,L20:O20,J13")
    'database table
    Set tbl = ThisWorkbook.Worksheets("Event Database").ListObjects(1)
 
    'check form not blank
    If Application.CountA(rngEntry) > 0 Then
     
        'size array
        ReDim arr(1 To rngEntry.Cells.Count)
     
        'add entry to array
        For Each cell In rngEntry.Cells
            i = i + 1
            arr(i) = cell.Value
        Next cell
     
        'Add New row to the table
        Set NewRecord = tbl.ListRows.Add(AlwaysInsert:=True)
        'post array to table
        NewRecord.Range(1).Resize(, UBound(arr)).Value = arr
     
        'clear entry form
        rngEntry.Value = ""
        'inform user
        MsgBox "New Record Saved To Database", 64, "New Record"
    End If
 
End Sub

24-01-2023.xls
	A	B	C	D	E	F	G	H	I	J
1	Date	Test 1	Test 2	Test 3	Hrs	Test 4	Test 4a	Test 4b	Test 4c	Comments
2	01/01/2023	ab	mn	Liasion	6	Mike	Steve	Steve	Scott	Thank You for your help, it much appreciated!
Event Database

Change name of your commandbutton as required

Dave

 

[fish0321]

Dave, it looks like I get an error " invalid use of Me keyword". I am running Excel 2016

 

[fish0321]

Dave, it looks like I get an error " invalid use of Me keyword". I am running Excel 2016

Dave,

I created a new command button in which i no longer get a Me.Range error; however, I now get a the following problem, and yes it is a table. to add more frustration to my project, how would I have the Event page fields clear once the data is transferred to the Event Database with a popup message stating data has been entered. Once again, Thanks for your help!

 

[dmt32]

Hi,
as stated, the code goes in your entry forms worksheet code page.
Solution clears the form once data is posted to the database & msgbox will display telling user record has been saved

'clear entry form
        rngEntry.Value = ""
        'inform user
        MsgBox "New Record Saved To Database", 64, "New Record"

Dave