I have 2 workbooks wich require the same data to be entered into them each
month (Each workbook has a different end purpose). In the past I have tried
linking the 2 workbooks by formula but because the workbooks are obiviously
saved in 2 different places, the links sometimes for one reason or another
break. I was wondering if there was any way a macro could be written on one
workbook that would place the data in the relevant worksheet tab & cells of
the other one or would this be too complicated?!

--
Lois

This is done all the time. it is simple to open one workbook from antoher
and move the data.

Below is a very simple case of one workbook opening another and copying some
data

You can record a macro while performing the operations and then re-use the
macro when you need to perform the operations again. I would reecommend
editing the recorded macro when you get done because the recorder creates a
lot of extra statements that aren't required. I can reduce the recorder
macro when you get done.

Sub test()

Set BK1 = ThisWorkbook
Set Bk1Sht = BK1.Sheets("Sheet1")
BookName = "c:\temp\book1.xls"
Set Bk2 = Workbooks.Open(Filename:=BookName)
Set Bk2Sht = Bk2.Sheets("Sheet1")

With BK1
BK1Sht.Range("A1") = Bk2Sht.Range("C4")

BkSht2.Range("B7:D10").Copy _
Destination:=Bk1Sht.Range("B9")

End With

Bk2.clise savechanges:=False

End Sub



"Lois" wrote:

I have 2 workbooks wich require the same data to be entered into them each
month (Each workbook has a different end purpose). In the past I have tried
linking the 2 workbooks by formula but because the workbooks are obiviously
saved in 2 different places, the links sometimes for one reason or another
break. I was wondering if there was any way a macro could be written on one
workbook that would place the data in the relevant worksheet tab & cells of
the other one or would this be too complicated?!

--
Lois

Thanks, i will try this & see if i can get it to work!
will let you know
--
Lois


"joel" wrote:

This is done all the time. it is simple to open one workbook from antoher
and move the data.

Below is a very simple case of one workbook opening another and copying some
data

You can record a macro while performing the operations and then re-use the
macro when you need to perform the operations again. I would reecommend
editing the recorded macro when you get done because the recorder creates a
lot of extra statements that aren't required. I can reduce the recorder
macro when you get done.

Sub test()

Set BK1 = ThisWorkbook
Set Bk1Sht = BK1.Sheets("Sheet1")
BookName = "c:\temp\book1.xls"
Set Bk2 = Workbooks.Open(Filename:=BookName)
Set Bk2Sht = Bk2.Sheets("Sheet1")

With BK1
BK1Sht.Range("A1") = Bk2Sht.Range("C4")

BkSht2.Range("B7:D10").Copy _
Destination:=Bk1Sht.Range("B9")

End With

Bk2.clise savechanges:=False

End Sub



"Lois" wrote:

I have 2 workbooks wich require the same data to be entered into them each
month (Each workbook has a different end purpose). In the past I have tried
linking the 2 workbooks by formula but because the workbooks are obiviously
saved in 2 different places, the links sometimes for one reason or another
break. I was wondering if there was any way a macro could be written on one
workbook that would place the data in the relevant worksheet tab & cells of
the other one or would this be too complicated?!

--
Lois