Program to copy old weekly status sheet to new one

[davegb]

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(before:=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.

 

[Joel]

why don't you just copy the entire worksheet

Sheets("Sheet3").Copy After:=Sheets(Sheets.Count)

This will preserve to formating. You can then delete items from thne old
worksheet that needs to get entered each week.

 

[davegb]

why don't you just copy the entire worksheet

Sheets("Sheet3").Copy After:=Sheets(Sheets.Count)

This will preserve to formating.  You can then delete items from thne old
worksheet that needs to get entered each week.









- Show quoted text -

Because I want to make changes.

 

[Joel]

Make the changes after you copy. This way your preserve the formatting.

 

[Dave Peterson]

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]

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










--

Dave Peterson- Hide quoted text -

- Show quoted text -

That's the problem. A1, B1, and C1 all use different rules. But the
resulting colors are the same. So I'm trying to figure out a way to
get the color data across without having to do it manually. Could this
be the first thing I've found that XL VBA can't do?

 

[davegb]

That's the problem. A1, B1, and C1 all use different rules. But the
resulting colors are the same. So I'm trying to figure out a way to
get the color data across without having to do it manually. Could this
be the first thing I've found that XL VBA can't do?- Hide quoted text -

- Show quoted text -

I just thought of another approach. What if I copied the formatting
from cell A! into the others, but had VBA enter the correct formulas
into B1 and C1? Is that more doable? How would I do that?

 

[Dave Peterson]

Adjusting the existing rules is a real pain in the butt (if my memory is
correct).

I'd record a macro that did exactly what you wanted and you should have code
that could be used for B1 and C1 (I think???).