View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Program to copy old weekly status sheet to new one

Untested...

Can you just select the worksheet, then select the range (B1:C1), then show the
dialog.

If B1 and C1 use different rules, then this won't work.

With wsNewWklySht
.Select
.Range("B1:C1").Select
Application.Dialogs(xlDialogConditionalFormatting) .Show
End With

davegb wrote:

I have a weekly status sheet I re-create each week. I'm writing code
to do it. So far, it creates the new sheet and copies the formatting
from the first 3 cells of the old sheet into the new one. They all use
conditional formatting. What I want to do next is open the conditional
formatting dialog and enter new colors for the font and background,
then close the dialog and have the macro continue and copy the colors
I manually selected to the other cells (B1 and C1), but leave the
conditions intact. Does anyone know how to do this?
Here is the code thus far:
Sub NewWklySht()
Dim wsNewWklySht As Worksheet
Dim wsCurWklySht As Worksheet

Dim NewShtName As String

Set wsCurWklySht = ActiveSheet
NewShtName = Format(CDate(ActiveSheet.Name) + 7, "mmm dd")
Worksheets.Add(befo=Sheets("Project Summary")).Name = NewShtName
Set wsNewWklySht = Worksheets(NewShtName)

wsCurWklySht.Range("A1:C1").Copy
wsNewWklySht.Range("A1:C1").PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
wsCurWklySht.Range("C1").Copy
wsNewWklySht.Range("C1").PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Thanks in advance.


--

Dave Peterson