Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search, copy and paste through multiple sheets | Excel Discussion (Misc queries) | |||
Copy data from multiple sheets into new sheet | Excel Worksheet Functions | |||
copy from one sheet and paste into other sheets | Excel Discussion (Misc queries) | |||
multiple copy / paste on locked sheet | Excel Programming | |||
copy and paste from different sheets into one sheet using a VB code | Excel Programming |