Imbedded Data Input sheet

  • Thread starter Thread starter rowanmk
  • Start date Start date
R

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
 
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
Click to expand...

(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
Click to expand...

"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
Click to expand...

(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).
 
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
 
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 :
 
Back
Top