copy range to multiple workbooks

[davez]

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.

 

[Guest]

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

 

[Ron de Bruin]

Hi davez

See
http://www.rondebruin.nl/copy4.htm

 

[davez]

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]

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("V1K30").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