Previous Topic Next Topic

Excel Automated Worksheets?!

Closed
65sickster
Posts
1
Registration date
Wednesday December 16, 2009
Status
Member
Last seen
December 17, 2009
 - Dec 17, 2009 at 05:44 PM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 - Dec 17, 2009 at 08:58 PM
Hey everyone,

I am working with excel 2004 for a mac, and I am pretty new to using excel outside of basic formulas in the formula bar.

That being said, I have a spreadsheet with 4 worksheets within it. Each worksheet has the same first column, which consist of about 107 account names for my business.

What I've done is created multiple worksheets within the spreadsheet - one for contact info, one for financial info, one for season projections, etc - this at least keeps the sheet from being the size of Wyoming. But here is my question:

Is it possible to automate the spreadsheet so that any change to the account list in Column A on sheet 1 will automatically change the account list in Column A in all other sheets? Also, if there is a whole row added in the first sheet, can that be replicated in all other worksheets as well?

Thanks for the help in advance!

Erik

1 reply

Answer 1 / 1
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 805
Dec 17, 2009 at 08:58 PM
reg change of values in column A

right click the sheet1 tab and click viewcode and in the window that comes up copy this event code 

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo stage
Dim j As Integer, k As Integer, r As Integer, c As Integer
j = Worksheets.Count
'MsgBox j
If Target.Column <> 1 Then Exit Sub
r = Target.Row
c = Target.Column
Target.Copy
For k = 1 To 3
'MsgBox Sheets(k).Name
If Sheets(k).Name = "Sheet1" Then GoTo nnext
Worksheets(k).Cells(r, c).PasteSpecial
nnext:
Next k
Application.CutCopyMode = False
stage:
Application.EnableEvents = True
End Sub


test a few cases

regarding introducing a new row
keeping control key down click all the tabs. these will turn to white background
now if you type a row it will be automatically entered in all the sheets in the same row
REMEMBER to click one of the now turned white tabs so that the sheets are deselected and only sheet1 is selected.


my versions of excel is windows xp excel 2002.

confirm whether you got what you want
0