Home |
Search |
Today's Posts |
#1
|
|||
|
|||
copy range to multiple workbooks
Hi all, I have a good search around but have been unable to find what I am after, that being - I wish to copy a range - V1 to BK30 - from a single worksheet in what is my master workbook -"new_rtgs"- to any number of other workbooks which are copies made daily of the master workbook & which contain up to 30 worksheets. Basicaly this range contains formulas which analyse data I import to Colums A thru U. All the workbooks I need to copy to are housed in the same directory & are named by date - eg aug_01, aug_02 etc. Thanks in advance for any help provided. -- davez ------------------------------------------------------------------------ davez's Profile: http://www.excelforum.com/member.php...o&userid=16685 View this thread: http://www.excelforum.com/showthread...hreadid=399344 |
#2
|
|||
|
|||
hi,
do you wish this pasted in all workbook in the directory? on which sheet? since these workbooks are copies i assume that you want it pasted in the same place. correct? Regards FSt1 "davez" wrote: Hi all, I have a good search around but have been unable to find what I am after, that being - I wish to copy a range - V1 to BK30 - from a single worksheet in what is my master workbook -"new_rtgs"- to any number of other workbooks which are copies made daily of the master workbook & which contain up to 30 worksheets. Basicaly this range contains formulas which analyse data I import to Colums A thru U. All the workbooks I need to copy to are housed in the same directory & are named by date - eg aug_01, aug_02 etc. Thanks in advance for any help provided. -- davez ------------------------------------------------------------------------ davez's Profile: http://www.excelforum.com/member.php...o&userid=16685 View this thread: http://www.excelforum.com/showthread...hreadid=399344 |
#3
|
|||
|
|||
Hi davez
See http://www.rondebruin.nl/copy4.htm -- Regards Ron de Bruin http://www.rondebruin.nl "davez" wrote in message ... Hi all, I have a good search around but have been unable to find what I am after, that being - I wish to copy a range - V1 to BK30 - from a single worksheet in what is my master workbook -"new_rtgs"- to any number of other workbooks which are copies made daily of the master workbook & which contain up to 30 worksheets. Basicaly this range contains formulas which analyse data I import to Colums A thru U. All the workbooks I need to copy to are housed in the same directory & are named by date - eg aug_01, aug_02 etc. Thanks in advance for any help provided. -- davez ------------------------------------------------------------------------ davez's Profile: http://www.excelforum.com/member.php...o&userid=16685 View this thread: http://www.excelforum.com/showthread...hreadid=399344 |
#4
|
|||
|
|||
thanks for the link Ron, unfortunatley the macro only enables copy to the 1st worksheet within the target workbooks, where as i need to copy from the master to all worksheets within the target workbooks. i assume the line of code below sets the collection index to 1, as highlighted in bold Set destrange = mybook.*Worksheets(1)*.Range("a1") if anyone can advise how to change this code so that all the worksheets within the workbook are selected & pasted to, i will be eternally grateful! -- davez ------------------------------------------------------------------------ davez's Profile: http://www.excelforum.com/member.php...o&userid=16685 View this thread: http://www.excelforum.com/showthread...hreadid=399344 |
#5
|
|||
|
|||
here is the completed code adapted for my purposes of course - Sub copyPasteToMultiples() Dim myDir As String Dim myFile As String Dim ws As Worksheet Application.ScreenUpdating = False myDir = "C:\test\" 'Where the files are myFile = Dir(myDir & "*.xls") 'Find the first .xls file Do While myFile < "" Workbooks("new_rtgs.xls").Worksheets(1).Range("V1: DK30").copy Workbooks.Open (myDir & myFile) For Each ws In ActiveWorkbook.Worksheets ws.Range("V1").PasteSpecial Next ws ActiveWorkbook.Close -1 myFile = Dir 'Find the next file Loop Application.CutCopyMode = False Application.ScreenUpdating = True End Sub -- davez ------------------------------------------------------------------------ davez's Profile: http://www.excelforum.com/member.php...o&userid=16685 View this thread: http://www.excelforum.com/showthread...hreadid=399344 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy and paste between workbooks | Excel Discussion (Misc queries) | |||
How do you copy RANK w/o it changing the range? | Excel Worksheet Functions | |||
reminder notifications in a column | Excel Discussion (Misc queries) | |||
Copy multiple charts from Excel to Power Point | Charts and Charting in Excel |