Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Formatting | Excel Discussion (Misc queries) | |||
Worksheet formatting | Excel Worksheet Functions | |||
Worksheet Formatting | Excel Worksheet Functions | |||
Formatting Worksheet | Excel Programming | |||
formatting a worksheet | Excel Programming |