If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Default Workbook
Hey,
I appreciate your input and responding so quickly. That sent me down the macro path so Dave's suggestion dovetailed nicely. In case it will help anyone else, I had to modify the macro slightly. I found that when the worksheet is hidden, the 'Sheets("Sheet").Copy After:=Sheets(Sheets.Count)' function errors out because it cannot find the hidden sheet. To modify it, I've added code to un-hide the sheet before copying it, and then hiding it again after copying it. The scripts now look like this: Sub NewSheet() Sheet1.Visible = True Sheets("Sheet").Select Sheets("Sheet").Copy After:=Sheets(Sheets.Count) Sheet1.Visible = xlVeryHidden End Sub Sub HideSheetTemplate() Sheet1.Visible = xlVeryHidden End Sub Sub ShowSheetTemplate() Sheet1.Visible = True End Sub "Gord Dibben" wrote: Excellent and I am happy Dave came up with a better solution with the hidden worksheet. Gord |
#12
|
|||
|
|||
Default Workbook
If you add:
application.screenupdating = false your code to unhide, copy, and hide application.screenupdating = true You won't see the flickering. And you could drop this line: Sheets("Sheet").Select You didn't do anything once you selected it. Pugs wrote: Hey, I appreciate your input and responding so quickly. That sent me down the macro path so Dave's suggestion dovetailed nicely. In case it will help anyone else, I had to modify the macro slightly. I found that when the worksheet is hidden, the 'Sheets("Sheet").Copy After:=Sheets(Sheets.Count)' function errors out because it cannot find the hidden sheet. To modify it, I've added code to un-hide the sheet before copying it, and then hiding it again after copying it. The scripts now look like this: Sub NewSheet() Sheet1.Visible = True Sheets("Sheet").Select Sheets("Sheet").Copy After:=Sheets(Sheets.Count) Sheet1.Visible = xlVeryHidden End Sub Sub HideSheetTemplate() Sheet1.Visible = xlVeryHidden End Sub Sub ShowSheetTemplate() Sheet1.Visible = True End Sub "Gord Dibben" wrote: Excellent and I am happy Dave came up with a better solution with the hidden worksheet. Gord -- Dave Peterson |
#13
|
|||
|
|||
Default Workbook
Roger that.
Thank you! "Dave Peterson" wrote: If you add: application.screenupdating = false your code to unhide, copy, and hide application.screenupdating = true You won't see the flickering. And you could drop this line: Sheets("Sheet").Select You didn't do anything once you selected it. |
|
Thread Tools | |
Display Modes | |
|
|