These two statements seem at odds to me ...

I have "ZCA" as my master sheet, if there is a new account name added i
update the ZCA sheet. I want all sheets be updated everytime i make changes
in ZCA sheets.

Do you want to update ZCA if any other sheet changes, or update the other
sheets if ZCA changes? If the latter, how do you know which sheet it applies
to?



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jhong" wrote in message
...
Hi Bob,

Thanks for the reply. I have several sheets within a workbook. Column
A of every sheet is where the account names are located. I have "ZCA"
as my master sheet, if i there is a new account name added i update
the ZCA sheet. I want all sheets be updated everytime i make changes
in ZCA sheets.

Thanks again..


On Mar 10, 5:11 pm, "Bob Phillips" wrote:
Rows don't have a name, so what exactly do you mean?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"jhong" wrote in message

...



Hi Everyone,

I found this code fromhttp://www.contextures.com/excelfilesref
FL0009, this macro is really good. The macro works by updating all
sheets when a new entry is updated in the main sheet. In the column A
of the main sheet the sheet you can find the list of sheet name
accross the file, so when you add changes, you provide the sheet name
and value then the changes will be sent to the corresponding sheet
name.

I am no good in vba, hope you can help me to edit this code to be
useful in my current work dilemma. Assuming that the column A of Main
sheet is not the sheet name of the other sheets but a row name from
the other sheets. And that any addition or changes from the rows of
main sheet will be updated to the other sheets of the workbook,
including the exect row number to specific. Column names from all
sheets including the main sheets the same.

Thanks tohttp://www.contextures.com/excelfilesfor providing good
materials!!

Thanks in advance!

Jerome

Sub FilterCities()

'last edited March 18, 2004
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:=False, 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("D1:D2"), _
CopyToRange:=wks.Range("A1").Offset(i, 0), _
Unique:=False
Else
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1:D2"), _
CopyToRange:=wks.Range("A1"), _
Unique:=False
End If
Next myCell

Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True

MsgBox "Data has been sent"

End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function- Hide quoted text -

- Show quoted text -

Thanks for the reply again Bob. I need to update all other remaining
sheets within the workbook if the ZCA changes.

On Mar 10, 8:17*pm, "Bob Phillips" wrote:
These two statements seem at odds to me ...

I have "ZCA" as my master sheet, if there is a new account name added i
update the ZCA sheet. I want all sheets be updated everytime i make changes
in ZCA sheets.

Do you want to update ZCA if any other sheet changes, or update the other
sheets if ZCA changes? If the latter, how do you know which sheet it applies
to?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jhong" wrote in message

...
Hi Bob,

Thanks for the reply. I have several sheets within a workbook. Column
A of every sheet is where the account names are located. I have "ZCA"
as my master sheet, if i there is a new account name added i update
the ZCA sheet. I want all sheets be updated everytime i make changes
in ZCA sheets.

Thanks again..

On Mar 10, 5:11 pm, "Bob Phillips" wrote:



Rows don't have a name, so what exactly do you mean?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"jhong" wrote in message

...

Hi Everyone,

I found this code fromhttp://www.contextures.com/excelfilesref
FL0009, this macro is really good. The macro works by updating all
sheets when a new entry is updated in the main sheet. In the column A
of the main sheet the sheet you can find the list of sheet name
accross the file, so when you add changes, you provide the sheet name
and value then the changes will be sent to the corresponding sheet
name.

I am no good in vba, hope you can help me to edit this code to be
useful in my current work dilemma. Assuming that the column A of Main
sheet is not the sheet name of the other sheets but a row name from
the other sheets. And that any addition or changes from the rows of
main sheet will be updated to the other sheets of the workbook,
including the exect row number to specific. Column names from all
sheets including the main sheets the same.

Thanks tohttp://www.contextures.com/excelfilesforproviding good
materials!!

Thanks in advance!

Jerome

Sub FilterCities()

'last edited March 18, 2004
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:=False, 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("D1:D2"), _
CopyToRange:=wks.Range("A1").Offset(i, 0), _
Unique:=False
Else
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1:D2"), _
CopyToRange:=wks.Range("A1"), _
Unique:=False
End If
Next myCell

Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True

MsgBox "Data has been sent"

End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -