Copy paste WkBk/sheet 1 to multiple wkbks/sheets
 

Good Morning
-I am at the point in my Excel experience of having a workabl
understanding of Excel. Now its on to Macros & then VBA.
-Index & Match works for me as the purpose underlying the followin
macro copy & paste excerpt. However it is verbose and totall
rudimentary and without the ability to be "volatile" I believe the VB
language calls the routine.
-My attempt is 1.) to open all wkbks and matching or correspondin
wksheets in all open wkbks, 2.) copy paste/special/values from lea
wkbk/sheet identified Range("A2:C61"). to "Range("P4:R63").Select" i
3 wksheets in multiple subservient wkbks/sheets without all th
scrolling & junk that exists in the following partial macro scrip
pasted below.
-Perhaps my biggest weakness is not understanding the routine o
language necessary to open all bks and corresponding sheets an
corresponding ranges only once for a cross wkbk/sh copy and paste o
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 FORM
BY TaxPayer\arp080105.XLS"
Workbooks.Open Filename:= _
"C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORM
BY TaxPayer\mep080105.XLS"
Workbooks.Open Filename:= _
"C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORM
BY TaxPayer\msp080105.XLS"
Workbooks.Open Filename:= _
"C:\Data\EXCEL\STOCKPROFITS\IN USE Actual STOCK GAIN-LOSS FORM
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").Activat

--
wrpalme
-----------------------------------------------------------------------
wrpalmer's Profile: http://www.excelforum.com/member.php...fo&userid=2534
View this thread: http://www.excelforum.com/showthread.php?threadid=39745

Copy paste WkBk/sheet 1 to multiple wkbks/sheets
 

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" wrote in
message ...

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