Below is an example of code I wrote to copy data input in
one file (the active file) to other files. I only show
one file being updated, but I have other routines that
update additional files. Could someone show me how to
use variables & loops to condense the code and make it
more efficient e.g., method of defining and looping
through specific sheets in a workbook and performing a
series of actions (not all sheets in the workbooked
should be updated & some are updated in different
columns)?

Any input on this or any other type of suggestions would
be appreciated. Kathryn



//////////////////

Option Explicit

' Set string to ID path and name of support files
' (note: "AnotherFile" is not used in the example
' - I just put it in to show that the routine
' will be updating several other files).
Global path_Psn As String, file_Psn As String
Global file_AnotherFile As String

' Set string to ID main file; file provides data&date
Global Myfile As String

' Set string/date to ID amounts$ copied to other files
Global pr_item1 As String, pr_item2 As String
Global pr_item3 As String, pr_item4 As String
Global pd_item1 As String, pd_item2 As String
Global pd_item3 As String, pd_item4 As String
Global pr_item5 As String, pd_item5 As String
Global MyDate As Date

Sub MyRoutine()
Call GetVariableInfo
Call UpdateFile1
' Call UpdateFile2 ' Not used in example
End Sub

Sub GetVariableInfo()

' Define variables
path_Psn = "c:\Documents and Settings\"
file_Psn = "Position.xls"
file_AnotherFile = "My Second File.xls"
Myfile = ActiveWorkbook.name

' Defined by range names in worksheet
Windows(Myfile).Activate
pr_item1 = -Range("pr_item1").Value
pr_item2 = -Range("pr_item2").Value
pr_item3 = -Range("pr_item3").Value
pr_item4 = -Range("pr_item4").Value
pd_item1 = Range("pd_item1").Value
pd_item2 = Range("pd_item2").Value
pd_item3 = Range("pd_item3").Value
pd_item4 = Range("pd_item4").Value
pr_item5 = Range("pr_item5").Value
pd_item5 = -Range("pd_item5").Value
MyDate = Range("b2").Value
End Sub

Sub UpdateFile1()
Workbooks.Open Filename:=path_Psn & file_Psn, _
UpdateLinks:=False
Sheets(2).Select
Cells.Find(What:=MyDate, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 2).Value = pd_item1
ActiveCell.Offset(0, 3).Value = pr_item1
Sheets(3).Select
Cells.Find(What:=MyDate, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 2).Value = pd_item2
ActiveCell.Offset(0, 3).Value = pr_item2
Sheets(4).Select
Cells.Find(What:=MyDate, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 2).Value = pd_item3
ActiveCell.Offset(0, 3).Value = pr_item3
Sheets(5).Select
Cells.Find(What:=MyDate, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 2).Value = pd_item4
ActiveCell.Offset(0, 3).Value = pr_item4
Sheets(1).Select
Cells.Find(What:=MyDate, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 7).Value = pr_item5
ActiveCell.Offset(0, 8).Value = pd_item5
End Sub

///////////////