Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program to copy old weekly status sheet to new one
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program to copy old weekly status sheet to new one
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" 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program to copy old weekly status sheet to new one
On Mar 27, 10:42*am, Joel wrote:
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" 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.- Hide quoted text - - Show quoted text - Because I want to make changes. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program to copy old weekly status sheet to new one
Make the changes after you copy. This way your preserve the formatting.
"davegb" wrote: On Mar 27, 10:42 am, Joel wrote: 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" 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.- Hide quoted text - - Show quoted text - Because I want to make changes. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program to copy old weekly status sheet to new one
On Mar 27, 1:10*pm, Dave Peterson wrote:
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- 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program to copy old weekly status sheet to new one
On Mar 27, 4:21*pm, davegb wrote:
On Mar 27, 1:10*pm, Dave Peterson wrote: 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- 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?- 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program to copy old weekly status sheet to new one
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???). davegb wrote: On Mar 27, 4:21 pm, davegb wrote: On Mar 27, 1:10 pm, Dave Peterson wrote: 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- 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?- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weekly student status | Excel Discussion (Misc queries) | |||
status update during program run -- DoEvents doesn't always work | Excel Programming | |||
with weekly score sheet how do I column a weekly progressive aver. | Excel Worksheet Functions | |||
Display Program Status | Excel Programming | |||
How to display a form- to show status of the running program | Excel Discussion (Misc queries) |