Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
I have created a macro to copy data from a worksheet; create a new worksheet
and paste the data. However, the new worksheet has conditional formatting; when I run the macro it deletes the worksheet along with the conditional formatting. Is there a way to append the new data and maintain the conditional formatting in the new worksheet so I dont have to re-enter the conditional formatting? Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
On Apr 28, 2:49*pm, Excel Help!
wrote: I have created a macro to copy data from a worksheet; create a new worksheet and paste the data. *However, the new worksheet has conditional formatting; when I run the macro it deletes the worksheet along with the conditional formatting. *Is there a way to append the new data and maintain the conditional formatting in the new worksheet so I don’t have to re-enter the conditional formatting? Thanks in advance for any help. Could you just pasteSpecial(XlVlaues)? that way you keep the formatting. Jay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
I'm using it. The problem is I'm creating a new worksheet each time the
macro runs. Should I find a way to have the macro use the same worksheet each time; delete the data and paste? Thanks for your help. "jlclyde" wrote: On Apr 28, 2:49 pm, Excel Help! wrote: I have created a macro to copy data from a worksheet; create a new worksheet and paste the data. However, the new worksheet has conditional formatting; when I run the macro it deletes the worksheet along with the conditional formatting. Is there a way to append the new data and maintain the conditional formatting in the new worksheet so I dont have to re-enter the conditional formatting? Thanks in advance for any help. Could you just pasteSpecial(XlVlaues)? that way you keep the formatting. Jay |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Currently you copy data from a worksheet.............right?
The you create a new worksheet via code.........right? That worksheet has conditional formatting...............right? How did it get the CF? Did you add the CF through your code when you created the new worksheet? Maybe add the CF after you have pasted the copied data? How does your code "delete the worksheet"? Time to post the code, I think. Gord Dibben MS Excel MVP On Tue, 29 Apr 2008 06:23:01 -0700, Excel Help! wrote: I'm using it. The problem is I'm creating a new worksheet each time the macro runs. Should I find a way to have the macro use the same worksheet each time; delete the data and paste? Thanks for your help. "jlclyde" wrote: On Apr 28, 2:49 pm, Excel Help! wrote: I have created a macro to copy data from a worksheet; create a new worksheet and paste the data. However, the new worksheet has conditional formatting; when I run the macro it deletes the worksheet along with the conditional formatting. Is there a way to append the new data and maintain the conditional formatting in the new worksheet so I don’t have to re-enter the conditional formatting? Thanks in advance for any help. Could you just pasteSpecial(XlVlaues)? that way you keep the formatting. Jay |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Here it goes.... Thanks for any inputs. I'm not a code writer.
Sub Copy_With_AutoFilter1() Dim WS As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim rng2 As Range With Application .ScreenUpdating = False .EnableEvents = False End With Set WS = Sheets("AF") '<<< Change Set rng = WS.Range("A1:J" & Rows.Count) WS.AutoFilterMode = False On Error Resume Next Application.DisplayAlerts = False Sheets("25IS").Delete Application.DisplayAlerts = True On Error GoTo 0 rng.AutoFilter Field:=4, Criteria1:="=USA-560", Operator:=xlOr, Criteria2:="=SCIF-0000000075" Set WSNew = Worksheets.Add WSNew.Name = "25IS" WS.AutoFilter.Range.Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher .PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With WS.AutoFilterMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Gord Dibben" wrote: Currently you copy data from a worksheet.............right? The you create a new worksheet via code.........right? That worksheet has conditional formatting...............right? How did it get the CF? Did you add the CF through your code when you created the new worksheet? Maybe add the CF after you have pasted the copied data? How does your code "delete the worksheet"? Time to post the code, I think. Gord Dibben MS Excel MVP On Tue, 29 Apr 2008 06:23:01 -0700, Excel Help! wrote: I'm using it. The problem is I'm creating a new worksheet each time the macro runs. Should I find a way to have the macro use the same worksheet each time; delete the data and paste? Thanks for your help. "jlclyde" wrote: On Apr 28, 2:49 pm, Excel Help! wrote: I have created a macro to copy data from a worksheet; create a new worksheet and paste the data. However, the new worksheet has conditional formatting; when I run the macro it deletes the worksheet along with the conditional formatting. Is there a way to append the new data and maintain the conditional formatting in the new worksheet so I dont have to re-enter the conditional formatting? Thanks in advance for any help. Could you just pasteSpecial(XlVlaues)? that way you keep the formatting. Jay |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Rather than delete the copy-to sheet then add a new sheet, you could just
clearcontents before copying the new values. This was tested on a workbook with the changes seen. Adjust to your needs. Sub Copy_With_AutoFilter1() Dim WS As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim rng2 As Range With Application .ScreenUpdating = False .EnableEvents = False End With Set WS = Sheets("Day") '<<< Change Set rng = WS.Range("A1:J" & Rows.Count) Set WSNew = Worksheets("Sheet6") WS.AutoFilterMode = False On Error Resume Next Application.DisplayAlerts = False WSNew.Cells.ClearContents Application.DisplayAlerts = True On Error GoTo 0 rng.AutoFilter Field:=4, Criteria1:="2Off", Operator:=xlOr, _ Criteria2:="2Night" WS.AutoFilter.Range.Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher .PasteSpecial Paste:=8 .PasteSpecial xlPasteValues Application.CutCopyMode = False .Select End With WS.AutoFilterMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Gord On Tue, 29 Apr 2008 11:11:01 -0700, Excel Help! wrote: Here it goes.... Thanks for any inputs. I'm not a code writer. Sub Copy_With_AutoFilter1() Dim WS As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim rng2 As Range With Application .ScreenUpdating = False .EnableEvents = False End With Set WS = Sheets("AF") '<<< Change Set rng = WS.Range("A1:J" & Rows.Count) WS.AutoFilterMode = False On Error Resume Next Application.DisplayAlerts = False Sheets("25IS").Delete Application.DisplayAlerts = True On Error GoTo 0 rng.AutoFilter Field:=4, Criteria1:="=USA-560", Operator:=xlOr, Criteria2:="=SCIF-0000000075" Set WSNew = Worksheets.Add WSNew.Name = "25IS" WS.AutoFilter.Range.Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher .PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With WS.AutoFilterMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Gord Dibben" wrote: Currently you copy data from a worksheet.............right? The you create a new worksheet via code.........right? That worksheet has conditional formatting...............right? How did it get the CF? Did you add the CF through your code when you created the new worksheet? Maybe add the CF after you have pasted the copied data? How does your code "delete the worksheet"? Time to post the code, I think. Gord Dibben MS Excel MVP On Tue, 29 Apr 2008 06:23:01 -0700, Excel Help! wrote: I'm using it. The problem is I'm creating a new worksheet each time the macro runs. Should I find a way to have the macro use the same worksheet each time; delete the data and paste? Thanks for your help. "jlclyde" wrote: On Apr 28, 2:49 pm, Excel Help! wrote: I have created a macro to copy data from a worksheet; create a new worksheet and paste the data. However, the new worksheet has conditional formatting; when I run the macro it deletes the worksheet along with the conditional formatting. Is there a way to append the new data and maintain the conditional formatting in the new worksheet so I don’t have to re-enter the conditional formatting? Thanks in advance for any help. Could you just pasteSpecial(XlVlaues)? that way you keep the formatting. Jay |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
For some reason it adjusted the format of the data (header), but it works.
My last question: Is it possible to have the macro prompt the user to enter the search Criteria data. This way I would not have to write the same code 10 times to match each user's request? Thanks again for the assistances. "Gord Dibben" wrote: Rather than delete the copy-to sheet then add a new sheet, you could just clearcontents before copying the new values. This was tested on a workbook with the changes seen. Adjust to your needs. Sub Copy_With_AutoFilter1() Dim WS As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim rng2 As Range With Application .ScreenUpdating = False .EnableEvents = False End With Set WS = Sheets("Day") '<<< Change Set rng = WS.Range("A1:J" & Rows.Count) Set WSNew = Worksheets("Sheet6") WS.AutoFilterMode = False On Error Resume Next Application.DisplayAlerts = False WSNew.Cells.ClearContents Application.DisplayAlerts = True On Error GoTo 0 rng.AutoFilter Field:=4, Criteria1:="2Off", Operator:=xlOr, _ Criteria2:="2Night" WS.AutoFilter.Range.Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher .PasteSpecial Paste:=8 .PasteSpecial xlPasteValues Application.CutCopyMode = False .Select End With WS.AutoFilterMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Gord On Tue, 29 Apr 2008 11:11:01 -0700, Excel Help! wrote: Here it goes.... Thanks for any inputs. I'm not a code writer. Sub Copy_With_AutoFilter1() Dim WS As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim rng2 As Range With Application .ScreenUpdating = False .EnableEvents = False End With Set WS = Sheets("AF") '<<< Change Set rng = WS.Range("A1:J" & Rows.Count) WS.AutoFilterMode = False On Error Resume Next Application.DisplayAlerts = False Sheets("25IS").Delete Application.DisplayAlerts = True On Error GoTo 0 rng.AutoFilter Field:=4, Criteria1:="=USA-560", Operator:=xlOr, Criteria2:="=SCIF-0000000075" Set WSNew = Worksheets.Add WSNew.Name = "25IS" WS.AutoFilter.Range.Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher .PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With WS.AutoFilterMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Gord Dibben" wrote: Currently you copy data from a worksheet.............right? The you create a new worksheet via code.........right? That worksheet has conditional formatting...............right? How did it get the CF? Did you add the CF through your code when you created the new worksheet? Maybe add the CF after you have pasted the copied data? How does your code "delete the worksheet"? Time to post the code, I think. Gord Dibben MS Excel MVP On Tue, 29 Apr 2008 06:23:01 -0700, Excel Help! wrote: I'm using it. The problem is I'm creating a new worksheet each time the macro runs. Should I find a way to have the macro use the same worksheet each time; delete the data and paste? Thanks for your help. "jlclyde" wrote: On Apr 28, 2:49 pm, Excel Help! wrote: I have created a macro to copy data from a worksheet; create a new worksheet and paste the data. However, the new worksheet has conditional formatting; when I run the macro it deletes the worksheet along with the conditional formatting. Is there a way to append the new data and maintain the conditional formatting in the new worksheet so I dont have to re-enter the conditional formatting? Thanks in advance for any help. Could you just pasteSpecial(XlVlaues)? that way you keep the formatting. Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |