Copy paste WkBk/sheet 1 to multiple wkbks/sheets

Good Morning
-I am at the point in my Excel experience of having a workable understanding of Excel. Now its on to Macros & then VBA.
-Index & Match works for me as the purpose underlying the following macro copy & paste excerpt. However it is verbose and totally rudimentary and without the ability to be "volatile" I believe the VBA language calls the routine.
-My attempt is 1.) to open all wkbks and matching or corresponding wksheets in all open wkbks, 2.) copy paste/special/values from lead wkbk/sheet identified Range("A2:C61"). to "Range("P4:R63").Select" in 3 wksheets in multiple subservient wkbks/sheets without all the scrolling & junk that exists in the following partial macro script pasted below.
-Perhaps my biggest weakness is not understanding the routine or language necessary to open all bks and corresponding sheets and corresponding ranges only once for a cross wkbk/sh copy and paste of values.
-Thanks to whomever has the wisdom & patience to address this
-wrpalmer

-Sub priceupdate()
'
' priceupdate Macro
' Macro recorded 8/9/2005 by William Palmer
'
' Keyboard Shortcut: Ctrl+u
'
Workbooks.Open Filename:= _
"C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORMS BY TaxPayer\arp080105.XLS"
Workbooks.Open Filename:= _
"C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORMS BY TaxPayer\mep080105.XLS"
Workbooks.Open Filename:= _
"C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORMS BY TaxPayer\msp080105.XLS"
Workbooks.Open Filename:= _
"C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORMS BY TaxPayer\sep080105.XLS"
Windows("PRICEUPDATE.xls").Activate
Range("A2:C61").Select
Selection.Copy
Windows("arp080105.XLS").Activate
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Range("P4:R63").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("PRICEUPDATE.xls").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("mep080105.XLS").Activate

Hi W,

The following is untested but, on copies, try:

'============================>>
Public Sub PriceUpdate()

Dim wb As Workbook
Dim myPath As String
Dim arr As Variant
Dim SrcBook As Workbook
Dim srcSheet As Worksheet
Dim destSheet As Worksheet
Dim myVal As Variant
Dim i As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

myPath = "C:\Data\EXCEL\STOCKPROFITS\" & _
"IN USE Actual STOCK GAIN-LOSS FORMS " & _
"BY TaxPayer\"

arr = Array("arp080105.XLS ", "mep080105.XLS", _
"msp080105.XLS", "sep080105.XLS")

Set SrcBook = Workbooks("PRICEUPDATE.xls")

Set srcSheet = SrcBook.Sheets("Sheet1") '<<===== CHANGE

myVal = secsheet.Range("A2:C61").Value

For i = LBound(arr) To UBound(arr)
Set wb = Workbooks.Open(myPath & arr(i))
Set destSheet = wb.Sheets("Sheet1")
wb.Range("P4:R63").Value = myVal '<<===== CHANGE
wb.Close SaveChanges:=True
Next i

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'============================>>

---
Regards,
Norman


"wrpalmer" <[email protected]> wrote in
message news:[email protected]...
>
> Good Morning
> -I am at the point in my Excel experience of having a workable
> understanding of Excel. Now its on to Macros & then VBA.
> -Index & Match works for me as the purpose underlying the following
> macro copy & paste excerpt. However it is verbose and totally
> rudimentary and without the ability to be "volatile" I believe the VBA
> language calls the routine.
> -My attempt is 1.) to open all wkbks and matching or corresponding
> wksheets in all open wkbks, 2.) copy paste/special/values from lead
> wkbk/sheet identified Range("A2:C61"). to "Range("P4:R63").Select" in
> 3 wksheets in multiple subservient wkbks/sheets without all the
> scrolling & junk that exists in the following partial macro script
> pasted below.
> -Perhaps my biggest weakness is not understanding the routine or
> language necessary to open all bks and corresponding sheets and
> corresponding ranges only once for a cross wkbk/sh copy and paste of
> values.
> -Thanks to whomever has the wisdom & patience to address this
> -wrpalmer
>
> -Sub priceupdate()
> '
> ' priceupdate Macro
> ' Macro recorded 8/9/2005 by William Palmer
> '
> ' Keyboard Shortcut: Ctrl+u
> '
> Workbooks.Open Filename:= _
> "C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORMS
> BY TaxPayer\arp080105.XLS"
> Workbooks.Open Filename:= _
> "C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORMS
> BY TaxPayer\mep080105.XLS"
> Workbooks.Open Filename:= _
> "C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORMS
> BY TaxPayer\msp080105.XLS"
> Workbooks.Open Filename:= _
> "C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORMS
> BY TaxPayer\sep080105.XLS"
> Windows("PRICEUPDATE.xls").Activate
> Range("A2:C61").Select
> Selection.Copy
> Windows("arp080105.XLS").Activate
> ActiveWindow.ScrollColumn = 7
> ActiveWindow.ScrollColumn = 8
> ActiveWindow.ScrollColumn = 9
> ActiveWindow.ScrollColumn = 10
> ActiveWindow.ScrollColumn = 11
> Range("P4:R63").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Windows("PRICEUPDATE.xls").Activate
> Application.CutCopyMode = False
> Selection.Copy
> Windows("mep080105.XLS").Activate
>
>
> --
> wrpalmer
> ------------------------------------------------------------------------
> wrpalmer's Profile:
> http://www.excelforum.com/member.php...o&userid=25347
> View this thread: http://www.excelforum.com/showthread...hreadid=397455
>