Imbedded Data Input sheet

[rowanmk]

Hi All,

I am looking to create a sheet where I can input data directly into the cell of sheet 1 (lets say for example, Date, Name, Date of Birth and Hair Color), click a button and it places the info into a table on sheet 2 and then the cells on sheet 1 go blank again.
I want this table on sheet 2 to be formatted as a table in Excel so lines are added to the "Range" and I can link graphs and tables based on this "source table".

Further, it would be awesome if the data can be put into a separate sheet based on the date (So all January birthdays appear in one table on Sheet 3 etc)...Not vital as this can be achieved through additional tables and Vlookups (maybe a messy way to deal with it).

Anyone able to help me with this?

Cheers
Rowan

 

[isabelle]

hi Rowan

firstly it would be useful to create the column titles (Date, Name, Date
of Birth and Hair Color) in sheet 1 and 2

secondly you need to create a name on Sheet 2, ref :
=INDIRECT("$A$1:$F"&COUNTA(Sheet2!$A$1:$A$65536))
you can use this name to create a pivot table on sheet 3
ps / adapt 65536 to your excel version, and "F" to your table

thirdly you need to link the following macro to a button

Sub Macro1()
Dim LastRow As Long
LastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count,
1).End(xlUp).Row + 1
Sheets("Sheet1").Range("A2:F2").Copy Sheets("Sheet2").Range("A" & LastRow)
End Sub

isabelle

Le 2013-02-24 08:04, (e-mail address removed) a écrit :

Hi All,

I am looking to create a sheet where I can input data directly into the cell of sheet 1

(lets say for example, Date, Name, Date of Birth and Hair Color),

click a button and it places the info into a table on sheet 2 and then
the cells on sheet 1 go blank again.

I want this table on sheet 2 to be formatted as a table in Excel so lines are added to the

"Range" and I can link graphs and tables based on this "source table".

Further, it would be awesome if the data can be put into a separate sheet based on the date

(So all January birthdays appear in one table on Sheet 3 etc)...Not
vital as this can be achieved

through additional tables and Vlookups (maybe a messy way to deal with
it).

 

[isabelle]

for not to overload the memory it would be good to add the command
Application.CutCopyMode = False

Sub Macro1()
Dim LastRow As Long
LastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count,
1).End(xlUp).Row + 1
Sheets("Sheet1").Range("A2:F2").Copy Sheets("Sheet2").Range("A" & LastRow)
Application.CutCopyMode = False
End Sub

isabelle

 

[isabelle]

correction for the name ref. :
=INDIRECT("Sheet2!$A$1:$F"&COUNTA(Sheet2!$A$1:$A$65536))

isabelle

Le 2013-03-05 01:33, isabelle a écrit :