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.

[FSt1]

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
>
>

[Ron de Bruin]

Hi davez

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"davez" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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("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