Macro Data Input

R

rbiggs

I have a worksheet that when a user enters the date it creates a new
worksheet. I need to have it so when they input additional information
on that page it does to that newly created worksheet. How can I make
this happen?
 
R

rbiggs

Here is the code that I have so far to do what I have described.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sDate As Variant
oldSheet$ = ActiveSheet.Name
sDate = Range("D2").Text
Filename =
"************************************************************************"

If Target.Address = "$D$2" Then
Sheets.Add(After:=Worksheets(Worksheets.Count - 2),
Type:=Filename).Name = "DOR " + sDate
End If
Sheets(oldSheet$).Activate
End Sub
 
R

rbiggs

Here is the code that I have so far to do what I have described.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sDate As Variant
oldSheet$ = ActiveSheet.Name
sDate = Range("D2").Text
Filename =
"************************************************************************"

If Target.Address = "$D$2" Then
Sheets.Add(After:=Worksheets(Worksheets.Count - 2),
Type:=Filename).Name = "DOR " + sDate
End If
Sheets(oldSheet$).Activate
End Sub
 
R

rbiggs

Here is the code that I have so far to do what I have described.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sDate As Variant
oldSheet$ = ActiveSheet.Name
sDate = Range("D2").Text
Filename =
"*********************************************************"

If Target.Address = "$D$2" Then
Sheets.Add(After:=Worksheets(Worksheets.Count - 2),
Type:=Filename).Name = "DOR " + sDate
End If
Sheets(oldSheet$).Activate
End Sub
 
R

rbiggs

Here is the code that I have so far to do what I have described.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sDate As Variant
oldSheet$ = ActiveSheet.Name
sDate = Range("D2").Text
Filename = "***********************"

If Target.Address = "$D$2" Then
Sheets.Add(After:=Worksheets(Worksheets.Count - 2),
Type:=Filename).Name = "DOR " + sDate
End If
Sheets(oldSheet$).Activate
End Sub
 
J

JMay

What are you wanting to do with the Filename = and the Type:=Filename -
I don't understand.
 
R

rbiggs

Since it is a template that they are creating when they enter the date
I thought I had to put in the file path. I just blanked it out since
it had mt company name and other information in the file name.

All I am trying to accomplish is a user puts in some data that will be
done everyday and it then populates the corresponding worksheet that he
has created when he put in the date above. I just dont understand how
to get the users input to the newly created worksheet.

Sorry about the confusion and thank you for your help.
 
J

JMay

Copy this code into the sheet1 code module (not a standard module):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewSheet As Worksheet
If Intersect(Target, Range("d2")) Is Nothing Then
Exit Sub
End If
Set NewSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count -
2))
Me.Cells.Copy NewSheet.Range("A1")
Sheets("Sheet1").Activate
End Sub

HTH
 
R

rbiggs

Thank you, now how to I pass data from that first worksheet to the one
that was just created?
 
J

JMay

The code I provided is to be in the code module of the sheet where you
Are entering the date in cell D2. Two lines from the end of the code
The line:
Me.Cells.Copy NewSheet.Range("A1")

"says... copy all the cells in this worksheet (Me.cells.copy)
And paste it into the newly created sheet Newsheet -- beginning at cell
A1.
 
Top