Hi there

I really hope you can help. A macro wont work so hopefully you can!

I want to automate copying values only of Worksheet "si" in the current open
work book to worksheet "ys" in workbook Master Copy which may or may not be
open.

Look forward to hearing back

Thanks

Nev

Start here (There is a example if the database sheet is in another workbook)
http://www.rondebruin.nl/copy1.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nev" wrote in message ...
Hi there

I really hope you can help. A macro wont work so hopefully you can!

I want to automate copying values only of Worksheet "si" in the current open
work book to worksheet "ys" in workbook Master Copy which may or may not be
open.

Look forward to hearing back

Thanks

Nev

Open both...
Copy 'si', switch to 'ys' in Master
Edit | Paste Special | Links | Paste Link

Save and close Master...
Whenever you open Master next, choose to update links...

"Nev" wrote:

Hi there

I really hope you can help. A macro wont work so hopefully you can!

I want to automate copying values only of Worksheet "si" in the current open
work book to worksheet "ys" in workbook Master Copy which may or may not be
open.

Look forward to hearing back

Thanks

Nev

Hi Sheeloo

Thanks for this. I can do it manually but wanted to have it as a macro/vba
so that my data entry clerk can do it and not get it wrong!

Nev

"Sheeloo" wrote:


Open both...
Copy 'si', switch to 'ys' in Master
Edit | Paste Special | Links | Paste Link

Save and close Master...
Whenever you open Master next, choose to update links...

"Nev" wrote:

Hi there

I really hope you can help. A macro wont work so hopefully you can!

I want to automate copying values only of Worksheet "si" in the current open
work book to worksheet "ys" in workbook Master Copy which may or may not be
open.

Look forward to hearing back

Thanks

Nev

Hi Ron

Not good with VBA but finally worked it out and it does just what I need.
Thanks.

I do have 1 problem and 1 question.

When I copy a cell which says 1 June 2009, after copying, it says 1 January
2009. The source cell is =DATE(YEAR(ys!D4)+C4,MONTH(ys!D4),DAY(ys!D4))

The formatting in source and destinantiuon is the same - English UK. This
is quite a critical point becaiuse the spreadsheet works off the dates.

My question is - how can I "Save As" after the transfer over.

I have modified the code to

Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Master Copy.xls") Then
Set DestWB = Workbooks("Master Copy.xls")
Else
Set DestWB = Workbooks.Open("C:\Nevs stuff\Back and front office
23-2-9\Block Management\Master Copy.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("si").Range("a1:t500")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("ys")



Set DestRange = DestSh.Range("a1:t500")

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value



With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Many thanks in advance for your help - its invaluable

Nev


"Ron de Bruin" wrote:

Start here (There is a example if the database sheet is in another workbook)
http://www.rondebruin.nl/copy1.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nev" wrote in message ...
Hi there

I really hope you can help. A macro wont work so hopefully you can!

I want to automate copying values only of Worksheet "si" in the current open
work book to worksheet "ys" in workbook Master Copy which may or may not be
open.

Look forward to hearing back

Thanks

Nev

Hi Nev

Send me you workbook private so I can take a look

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nev" wrote in message ...
Hi Ron

Not good with VBA but finally worked it out and it does just what I need.
Thanks.

I do have 1 problem and 1 question.

When I copy a cell which says 1 June 2009, after copying, it says 1 January
2009. The source cell is =DATE(YEAR(ys!D4)+C4,MONTH(ys!D4),DAY(ys!D4))

The formatting in source and destinantiuon is the same - English UK. This
is quite a critical point becaiuse the spreadsheet works off the dates.

My question is - how can I "Save As" after the transfer over.

I have modified the code to

Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Master Copy.xls") Then
Set DestWB = Workbooks("Master Copy.xls")
Else
Set DestWB = Workbooks.Open("C:\Nevs stuff\Back and front office
23-2-9\Block Management\Master Copy.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("si").Range("a1:t500")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("ys")



Set DestRange = DestSh.Range("a1:t500")

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value



With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Many thanks in advance for your help - its invaluable

Nev


"Ron de Bruin" wrote:

Start here (There is a example if the database sheet is in another workbook)
http://www.rondebruin.nl/copy1.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nev" wrote in message ...
Hi there

I really hope you can help. A macro wont work so hopefully you can!

I want to automate copying values only of Worksheet "si" in the current open
work book to worksheet "ys" in workbook Master Copy which may or may not be
open.

Look forward to hearing back

Thanks

Nev