formatting a worksheet
 

Is there a way I can automatically create a new formatted sheet based on a
template when a user enters data in a master sheet.

for example, the user enters data into sheet A, if data is entered into a
particular column sheet B should be automatically created based on some
standard formatting in hidden sheet X. It is likely that there will be more
than 1 sheet B.

Any help in providing a solution would be appreciated.

--
neil

formatting a worksheet
 

Seems plausible enough...how about a Worksheet_Change event?
1. Create your template on a seperate sheet and set its Hidden property
to xlSheetVeryHidden
2. Create a WorkSheet_Change event behind Sheet A; something like
this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.ScreenUpdating = False
With Sheet3
.Visible = xlSheetVisible
.Copy after:=Sheets(1)
.Visible = xlSheetVeryHidden
End With
ActiveSheet.Name = "Template" & (ThisWorkbook.Sheets.Count - 1)
Me.Activate
Application.ScreenUpdating = True
End If
End Sub

Should work...

Hth,
OJ

formatting a worksheet
 

You don't need to make a sheet visible to copy it.

--
Regards,
Tom Ogilvy


"OJ" wrote in message
oups.com...
Seems plausible enough...how about a Worksheet_Change event?
1. Create your template on a seperate sheet and set its Hidden property
to xlSheetVeryHidden
2. Create a WorkSheet_Change event behind Sheet A; something like
this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.ScreenUpdating = False
With Sheet3
.Visible = xlSheetVisible
.Copy after:=Sheets(1)
.Visible = xlSheetVeryHidden
End With
ActiveSheet.Name = "Template" & (ThisWorkbook.Sheets.Count - 1)
Me.Activate
Application.ScreenUpdating = True
End If
End Sub

Should work...

Hth,
OJ

formatting a worksheet
 

Tom

I am fairly new to vba, so I do not entirely understand the concepts that OJ
has tired to explain, but in any case could not get the code to work, can you
help.

thanks

"Tom Ogilvy" wrote:

You don't need to make a sheet visible to copy it.

--
Regards,
Tom Ogilvy


"OJ" wrote in message
oups.com...
Seems plausible enough...how about a Worksheet_Change event?
1. Create your template on a seperate sheet and set its Hidden property
to xlSheetVeryHidden
2. Create a WorkSheet_Change event behind Sheet A; something like
this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.ScreenUpdating = False
With Sheet3
.Visible = xlSheetVisible
.Copy after:=Sheets(1)
.Visible = xlSheetVeryHidden
End With
ActiveSheet.Name = "Template" & (ThisWorkbook.Sheets.Count - 1)
Me.Activate
Application.ScreenUpdating = True
End If
End Sub

Should work...

Hth,
OJ

formatting a worksheet
 

You sure about that Tom? If it is xlSheetVeryHidden then I think you do
have to make it visible before you copy it with this method. When I
remove the visible lines Excel throws up an error.....

Neil,
Create your TemplateSheet and hide it. Then...
RightClick over the sheet tab name for Sheet A and select 'View Code'.
Then paste in the code I wrote into the VB Window that comes up,
changing 'Sheet3' to 'Sheets("YourTemplateSheetNameInQuotesHere")'

Assuming Sheet A is the first sheet in your workbook, this will now
spit out a template sheet every time you manually change or add a value
in column B of Sheet A.

Personally, I would consider a command button to do this instead..

Hth,OJ

formatting a worksheet
 

I stand corrected. However, you can if it is hidden and Very Hidden really
doesn't buy you anything. Also, if you prefer very hidden, you can still
add a sheet and copy everything from the very hidden sheet without unhiding
it.

--
Regards,
Tom Ogilvy

"OJ" wrote in message
oups.com...
You sure about that Tom? If it is xlSheetVeryHidden then I think you do
have to make it visible before you copy it with this method. When I
remove the visible lines Excel throws up an error.....

Neil,
Create your TemplateSheet and hide it. Then...
RightClick over the sheet tab name for Sheet A and select 'View Code'.
Then paste in the code I wrote into the VB Window that comes up,
changing 'Sheet3' to 'Sheets("YourTemplateSheetNameInQuotesHere")'

Assuming Sheet A is the first sheet in your workbook, this will now
spit out a template sheet every time you manually change or add a value
in column B of Sheet A.

Personally, I would consider a command button to do this instead..

Hth,OJ