PC Review


Reply
Thread Tools Rate Thread

Copy conditional format from one sheet to another using VBA

 
 
KrisN
Guest
Posts: n/a
 
      23rd Jun 2008
I am currently using a great sample file written by Debra Dalgleish on her
website Excel Tips and Techniques. However, there is one thing I would like
for the file to do which I have unsuccefuly been able to do. I would like the
conditional format in the 'MAIN' sheet to carry over to all the other sheet
based on the column A; City Name. My conditional format shows-up in the Main
sheet but will not carry over to the other sheet. Only the value shows
without the conditional format. What VBA coding would be needed and where
would it reside in the following:
Sub FilterCities()
Dim myCell As Range
Dim wks As Worksheet
Dim DataBaseWks As Worksheet
Dim ListRange As Range
Dim dummyRng As Range
Dim myDatabase As Range
Dim TempWks As Worksheet
Dim rsp As Integer
Dim i As Long

'include bottom most header row
Const TopLeftCellOfDataBase As String = "A4"

'what column has your key values
Const KeyColumn As String = "A"

'where's your data
Set DataBaseWks = Worksheets("Main")
i = DataBaseWks.Range(TopLeftCellOfDataBase).Row - 1

rsp = MsgBox("Include headings?", vbYesNo, "Headings")

Set TempWks = Worksheets.Add

With DataBaseWks
Set dummyRng = .UsedRange
Set myDatabase = .Range(TopLeftCellOfDataBase, _
.Cells.SpecialCells(xlCellTypeLastCell))
End With

'rebuild the List
With DataBaseWks
Intersect(myDatabase, .Columns(KeyColumn)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), _
Unique:=True

'Add the heading to the criteria area
TempWks.Range("D1").Value = _
.Cells(.Range(TopLeftCellOfDataBase).Row, KeyColumn).Value
End With

With TempWks
Set ListRange = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ListRange
.Sort Key1:=.Cells(1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=True, Orientation:=xlTopToBottom
End With

'check for individual City worksheets
For Each myCell In ListRange.Cells
If WksExists(myCell.Value) = False Then
Set wks = Sheets.Add
On Error Resume Next
wks.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Please rename: " & wks.Name
Err.Clear
End If
On Error GoTo 0
wks.Move After:=Sheets(Sheets.Count)
Else
Set wks = Worksheets(myCell.Value)
wks.Cells.Clear
End If

If rsp = 6 Then
DataBaseWks.Rows("1:" & i).Copy Destination:=wks.Range("A1")
End If

'change the criteria in the Criteria range
TempWks.Range("D2").Value = "=" & Chr(34) & "=" & myCell.Value &
Chr(34)

'transfer data to individual City worksheets
If rsp = 6 Then
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D12"), _
CopyToRange:=wks.Range("A1").Offset(i, 0), _
Unique:=False
Else
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D12"), _
CopyToRange:=wks.Range("A1"), _
Unique:=False
End If

Any help would be most appreciated. Thanks Kris

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format from other sheet =?Utf-8?B?U2hlaWxhIEQ=?= Microsoft Excel Misc 2 28th Feb 2007 02:29 PM
How to copy a conditional format from one sheet to another? =?Utf-8?B?YmlsbHo=?= Microsoft Excel Misc 1 15th Feb 2007 10:33 PM
How do I copy a print format from sheet to sheet in excel ? =?Utf-8?B?a2VybmF0?= Microsoft Excel Misc 1 22nd Jul 2005 04:59 PM
conditional copy from sheet to sheet mlradak Microsoft Excel Discussion 1 3rd Oct 2004 09:54 AM
Conditional Copy From One Sheet To Another Kevin .. Microsoft Excel Programming 1 15th Jan 2004 06:06 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:00 AM.